Native PHP operation database addition, deletion, modification and query

Step one: Create a database and create a data table in the database. Of course, there can be many data tables in a database. Here I will create a table to store the students' personal names and grades.

Recommended mysql video tutorials: "mysql tutorial"

Idea: Connect to the server—>Create database—>Connect to database—>Create data Table

Script: Create database and data table

<?php
header("Content-type:text/html;charset=utf-8");
// 创建连接
$conn=mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;&#39;);//三个参数分别对应服务器名，账号，密码
// 检测连接
if (!$conn) {
    die("连接服务器失败: " . mysql_connect_error());//连接服务器失败退出程序
}
// 创建数据库命名为studentinfo
$sql_database = "CREATE DATABASE studentinfo";
if (mysql_query($sql_database,$conn)) {
    echo "数据库创建成功</br>";
} else {
    echo "数据库创建失败: " . mysql_error()."</br>";
}
//连接数据库studentinfo
$sele=mysql_select_db( &#39;studentinfo&#39; );
if(!$sele){
    die("连接数据库失败: ".mysql_error());//连接数据库失败退出程序
}
// 创建数据表命名为student，主键为id(不为空整型)，变量名为name(255位不为空字符串)，变量名为chinese(4位不为空整型)
//  变量名为english(4位不为空整型)，变量名为math(4位不为空整型)
$sql_table = "CREATE TABLE student( ".
       "id INT NOT NULL AUTO_INCREMENT, ".
       "name CHAR(255) NOT NULL, ".
       "chinese INT(4) NOT NULL, ".
       "english INT(4) NOT NULL, ".
       "math INT(4) NOT NULL, ".
       "PRIMARY KEY ( id )); ";
$retval = mysql_query( $sql_table, $conn );
if(! $retval ){
echo &#39;数据表创建失败: &#39; . mysql_error()."</br>";
}else{
echo "数据表创建成功</br>";
}
mysql_query(&#39;set names utf8&#39;);

mysql_close($conn);//关闭连接
?>

Now you can see the new database studentinfo and data table student

in phpMyAdmin

Step 2: Add student information data (increase) in the student data table of the studentinfo database

Idea: Connect to the server->Connect to the database->Insert specified data into the data table

Note: Because the previous PHP has already created a server connection and connected to the database, the following code omits the connection establishment part and writes function statements directly.

function addtabel_data(){
    //多维数组
    $datas=array(
      array("name"=>"测试猫","chinese"=>100,"english"=>100,"math"=>100),
      array("name"=>"测试狗","chinese"=>99,"english"=>99,"math"=>99),
      array("name"=>"测试虎","chinese"=>98,"english"=>98,"math"=>98)
    );
    for($i=0;$i<count($datas);$i++){
       $name=$datas[$i]["name"];
       $chinese=$datas[$i]["chinese"];
       $english=$datas[$i]["english"];
       $math=$datas[$i]["math"];
       //多维数组数据逐条插入student表
      mysql_query("insert into student(name,chinese,english,math) values (&#39;$name&#39;,$chinese,$english,$math)");
    }
    $res=mysql_affected_rows();//返回影响行
    if($res>0){
        echo "添加数据成功</br>";
    }else{
        echo "添加数据失败</br>";
    }
}
addtabel_data();//调用

When running php, I found that adding data failed. Why? Because a string with Chinese characters is passed in the name, and the name sorting rule defined in the student table is not utf-8? ? ?

It’s okay. We can modify the sorting rules with one click. After modifying it ourselves

, run it again. The data is added successfully and It is found that there is data in the table

Step 3: Query one or more specified pieces of information in the student table of the studentinfo database according to the query conditions (check)

Idea: Connect to the server -> Connect to the database -> Query the data table data according to the conditions

function selecttable_data($name){
    $res=mysql_query("select * from student where name='$name'");//根据name来查询student数据
//  $res=mysql_query("select * from student where name='$name' and chinese='$chinese'");//多条件查询连接符and
//  $res=mysql_query("select * from student");//查询student表里所有数据
//  $res=mysql_query("select * from student  limit 0,2“);//限制前面第1到2条数据
    if($res&&mysql_num_rows($res)){
        while($sql=mysql_fetch_assoc($res)){
            $arr[]=$sql;
        }
        echo json_encode($arr,JSON_UNESCAPED_UNICODE);//把数据(数组嵌套json类型)转换为字符串输出，这个ajax拿数据经常用
    }else{
        echo "找不到该数据</br>";
    }
}
selecttable_data("测试猫");//查询name为测试猫

Step 4: According to the modification conditions, the student in the studentinfo database Modify the specified data in the table (change)

Idea: Connect to the server->Connect to the database->Modify the specified data in the data table according to the conditions

function updatetabel_data($name,$chinese){
    mysql_query("update student set chinese='$chinese' where name='$name'");//修改student表里为$name的chinese数据修改为$chinese
    $res=mysql_affected_rows();//返回影响行
    if($res>0){
        echo "修改成功</br>";
    }else{
        echo "修改失败</br>";
    }
}
updatetabel_data("测试虎",90);//把测试虎的语文成绩修改为90分

Test Tiger Chinese score has changed from 98 Modify to 90

Step 5: Delete the specified data in the student table of the studentinfo database according to the deletion conditions (delete)

Idea: Connection Server—>Connect to the database—>Delete the specified data in the data table based on conditions

function deletetable_data($name){
    mysql_query("delete from student where name='$name'");//删除student表里为$name的整条数据
    $res=mysql_affected_rows();//返回影响行
    if($res>0){
        echo "删除成功</br>";
    }else{
        echo "删除失败</br>";
    }
}
deletetable_data(&#39;测试虎&#39;);//删除name为测试虎这条数据

##Test Tiger This data has been deleted