PHP database extension mysqli detailed usage tutorial

mysqli provides two ways to interact with the database, object-oriented and process-oriented. Let’s take a look at these two methods respectively.

Recommended related mysql video tutorials: "mysql tutorial"

1. Object-oriented

In the object-oriented approach, mysqli is encapsulated into a class, and its construction method is as follows:

__construct ([ string $host [, string $username [, string $passwd [, string $dbname[, int $port [, string $socket ]]]]]] )

In the above syntax The parameters involved are described below.

host: The connected server address.

username: The username to connect to the database. The default value is the username of the server process owner.

passwd: Password for connecting to the database, the default value is empty.

dbname: The name of the connected database.

port: TCP port number.

socket: UNIX domain socket.

To establish a connection with MySQL, you can instantiate the mysqli class through its constructor method, such as the following code:

<?php
    $db_host="localhost"; //连接的服务器地址
    $db_user="root"; //连接数据库的用户名
    $db_psw="root"; //连接数据库的密码
    $db_name="sunyang"; //连接的数据库名称
    $mysqli=new mysqli($db_host,$db_user,$db_psw,$db_name);
?>

mysqli also provides a member method connect() to connect to MySQL. When instantiating the mysqli class with an empty constructor, calling the connect() method with the mysqli object can also connect to MySQL. For example, the following code:

<?php
    $db_host="localhost"; //连接的服务器地址
    $db_user="root"; //连接数据库的用户名
    $db_psw="root"; //连接数据库的密码
    $db_name="sunyang"; //连接的数据库名称
    $mysqli=new mysqli();
    $mysqli->connect($db_host,$db_user,$db_psw,$db_name);
?>

Close the connection with the MySQL server by calling the close() method with the mysqli object , for example:

$mysqli->close();

2. Process-oriented

In the process-oriented way, the mysqli extension provides the function mysqli_connect() to establish a connection with MySQL. The syntax format of this function is as follows:

mysqli mysqli_connect ([ string $host [, string $username [, string $passwd[, string $dbname [, int $port [, string $socket ]]]]]] )

The usage of the mysqli_connect() function is the same as mysql The usage of the mysql_connect() function in the extension is very similar. The following is an example of the usage of the mysqli_connect() function:

<?php
    $connection = mysqli_connect("localhost","root","root","sunyang");
    if ( $connection ) {
        echo "数据库连接成功";
    }else {
        echo "数据库连接失败";
    }
?>

Use the mysqli_close() function to close the connection with the MySQL server, for example:

mysqli_close();

3. Use mysqli to access data

Using mysqli to access data also includes object-oriented and process-oriented methods. In this section we only discuss how to use the object-oriented method to interact with MySQL. The use of the process-oriented method in the mysqli extension will not be introduced in detail here. Interested readers can refer to the official documentation to obtain relevant information.

In mysqli, the query() method is used to execute queries. The syntax format of this method is as follows:

mixed query ( string $query [, int $resultmode ] )

The parameters involved in the above syntax are described as follows:

query: SQL statement sent to the server.

resultmode: This parameter accepts two values, one is MYSQLI_STORE_RESULT, which indicates that the result is returned as a buffered set; the other is MYSQLI_USE_RESULT, which indicates that the result is returned as a non-buffered set.

The following is an example of using the query() method to execute a query:

query($query);
    if ($result) {
        if($result->num_rows>0){ //判断结果集中行的数目是否大于0
            while($row =$result->fetch_array() ){ //循环输出结果集中的记录
                echo ($row[0])."
";
                echo ($row[1])."
";
                echo ($row[2])."
";
                echo ($row[3])."
";
                echo "
";
            }
        }
    }else {
        echo "查询失败";
    }
    $result->free();
    $mysqli->close();
?>

In the above code, num_rows is an attribute of the result set, returning the number of rows in the result set. The method fetch_array() puts the records in the result set into an array and returns it. Finally, use the free() method to release the memory in the result set, and use the close() method to close the database connection.

The operations of deleting records (delete), saving records (insert) and modifying records (update) are also performed using the query() method. The following is an example of deleting records:

query($query);
    if ($result){
        echo "删除操作执行成功";
    }else {
        echo "删除操作执行失败";
    }
    $mysqli->close();
?>

Save records (insert), modify The operation of recording (update) is similar to the operation of deleting records (delete). Just modify the SQL statement accordingly.

4. Prepared statements

Using prepared statements can improve the performance of reused statements. In PHP, use the prepare() method to query prepared statements, and use the execute() method to execute prepared statements. PHP has two types of prepared statements: one is to bind results and the other is to bind parameters.

(1) Binding result

The so-called binding result is to bind the custom variables in the PHP script to the corresponding fields in the result set. These variables represent the queried records. The sample code for the binding result is as follows ：

prepare($query); //进行预准备语句查询
    $result->execute(); //执行预准备语句
    $result->bind_result($id,$number,$name,$age); //绑定结果
    while ($result->fetch()) {
        echo $id;
        echo $number;
        echo $name;
        echo $age;
    }
    $result->close(); //关闭预准备语句
    $mysqli->close(); //关闭连接
?>

When binding results, the variables in the script must correspond one-to-one with the fields in the result set. After binding, use the fetch() method to fetch the variables bound in the result set one by one, and finally Preprocessing and database connections are closed separately.

(2) Binding parameters

The so-called binding parameters are to bind the custom variables in the PHP script to the parameters in the SQL statement (the parameters are replaced by "?"). The binding parameters use the bind_param() method. The syntax format of this method is as follows:

bool bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

The parameters involved in the above syntax are described as follows.

types: The data type of the bound variable. The character types it accepts include 4, as shown in the table below (the types of characters accepted by the parameter type and the bound variables need to correspond one-to-one).

var1: The number of bound variables must be consistent with the number of parameters in the SQL statement.

The sample code for binding parameters is as follows:

prepare($query);
    $result->bind_param("ssi",$number,$name,$age); //绑定参数
    $number='sy0807';
    $name='employee7';
    $age=20;
    $result->execute(); //执行预准备语句
    $result->close();
    $mysqli->close();
?>

You can also bind parameters and binding results at the same time in one script. The sample code is as follows:

prepare($query);
    $result->bind_param("i",$emp_id); //绑定参数
    $emp_id=4;
    $result->execute();
    $result->bind_result($id,$number,$name,$age); //绑定结果
    while ($result->fetch()) {
        echo $id."
";
        echo $number."
";
        echo $name."
";
        echo $age."
";
    }
    $result->close();
    $mysqli->close();
?>

5. Multiple queries

mysqli extension provides the ability to continuously execute multiple queries The multi_query() method of a query, the syntax format of this method is as follows:

bool mysqli_multi_query ( mysqli $link , string $query )

When executing multiple queries, except for the last query statement, each query statement must be separated by ";". Sample code to execute multiple queries is as follows:

 $mysqli=new mysqli("localhost","root","root","sunyang"); //实例化mysqli
    $query = "select emp_name from employee ;";
    $query .= "select dep_name from depment ";
    if ($mysqli->multi_query($query)) { //执行多个查询
        do {
            if ($result = $mysqli->store_result()) {
                while ($row = $result->fetch_row()) {
                    echo $row[0];
                    echo "
";
                }
                $result->close();
            }
            if ($mysqli->more_results()) {
                echo ("-----------------
"); //连个查询之间的分割线
            }
        } while ($mysqli->next_result());
    }
    $mysqli->close();//关闭连接
?>

在上述代码中，store_result()方法用于获得一个缓冲结果集； fetch_row()方法的作用类似于fetch_array()方法；more_results()方法用于从一个多查询中检查是否还有更多的查询结果；next_result()方法用于从一个多查询中准备下一个查询结果。

6、事务操作

首先只有数据库中表的类型为InnoDB时，才支持事务提交，建议使用InnoDB，更建议使用mysqli扩展库了，不仅因为mysqli支持多条sql查询，更是因为它的速度、性能、安全更可靠，而且完全面向对象，当然也可以是面向过程操作。

看下面mysqli对事务操作的php代码：

query("set names utf8");
    if ($mysqli->connect_error){
        die("连接错误：".$mysqli->connect_error);
    }
    //将事务提交设为false
    $mysqli->autocommit(false);
    $sql = "insert into `user` values(null,'小红',md5(123),'321321')";
    $sql2 = "insert into `user` values(null,'小王',md5(321),'dasf')";
    //执行操作,返回的是bool值
    $query = $mysqli->query($sql);
    $query2 = $mysqli->query($sql2);
 
    if ($query && $query2){
        $mysqli->commit();
        echo "操作成功";
    }else{
        echo "操作失败".$mysqli->error;
        $mysqli->rollback();
    }
    $mysqli->autocommit(true);
    $mysqli->close();
?>