PHP basic tutorial 14: Using MySqli to operate the database

Preface

In actual development, PHP will be used together with the database, because too much data needs to be saved in the background. The database is a good place to save data. The database we use for PHP development is the relational database mysql. Only when PHP and the mysql database are connected can we operate the database through the php code.

Recommended related mysql video tutorials: "mysql tutorial"

MySqli

The development of PHP is inseparable Database, and in PHP you can connect to the database through MySQLi. But MySQLi can only connect to the mysql database. At the same time, mysqli is an object-oriented technology.

Features of MySQLi:

• Improved efficiency and strong stability.

• Perform operations on the database.

• Support object-oriented development. It also supports process-oriented development.

If you want to use the MySQLi function in PHP, you need to load the dynamic connection file php_mysql.dll in php.ini.

Operation process

1. Create a database in mysql as the operation object.

2. Open the PHP extension library

3. Create the mysqli object

   $mysql = new MySQLi(主机，账号，密码，数据库，端口号)；

   There are several parameters in it.

4. Set the character set

   $mysql -> set_charset('utf8');

5. Write the sql statement and execute it. This sql statement can be dml, dql statement

   $mysql -> query($sql);

6. There are four ways to retrieve data from the retrieved result display page (assoc, row, object, array). We generally use assoc. Way. But if it is a dml statement, a Boolean value is returned.

   $res -> fetch_assoc();

7. Release the result set. Close the connection.

   $res -> free();
   $mysqli -> close();

When we are inserting, deleting, or modifying (dml), ​​a Boolean value is returned, but we do not know whether there is any change. You can use $mysqli -> affected_rows, the attributes in mysqli to judge. The returned result is the number of rows affected by the SQL statement on the data table.

<?php

    //使用面向对象进行数据库的连接，在创建对象的时候就自动的连接数据
    $mySQLi = new MySQLi(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,3306);

    //判断数据库是否连接
    if($mySQLi -> connect_errno){
        die(&#39;连接错误&#39; . $mySQLi -> connect_error);
    }
    //设置字符集
    $mySQLi -> set_charset(&#39;utf8&#39;);
    //编写sql语句并执行
    $sql = "select * from good";

    //发送sql语句并执行，如果是select语句，返回的是一个对象，其他的返回来一个boolean.
    $res = $mySQLi -> query($sql);

    echo &#39;<pre class="brush:php;toolbar:false">&#39;;
    //使用$res对象里面的fetch_assoc()取出里面的数据.
    // while($row = $res->fetch_assoc()){
    //  var_dump($row);
    // }
    // 使用fetch_row()方法
    // while($row = $res -> fetch_row()){
    //  var_dump($row);
    // }

    //使用fetch_array();
    // while($row = $res -> fetch_array()){
    //  var_dump($row);
    // }

    //fetch_object();
    while($row = $res -> fetch_object()){
        var_dump($row);
    }

    $res -> free();
    $mySQLi -> close();

The above code is the specific implementation using mysqli. mysqli is written using object-oriented thinking. About the method.

• $mySQLi -> connect_errno returns the error of the last connection. If 0 is returned, the connection is successful. If non-0 is returned, the connection fails.

• $mySQLi -> connect_error returns the reason for the connection error.

• $mySQLi -> set_charset(‘utf8’); Set the character set, and write the parameters according to your own situation.

• sql) After writing a sql statement, use this method to transfer the sql statement to the database for execution. And depending on the type of sql statement, different results are returned. The above returned is a mysqli_result object

  
  

• mysqli_result object represents the data obtained from a database query Result set. That is, the results returned from the database by sql query. The mysqli/_result object provides four methods to get the results inside, and they have different differences.

1. ##$mysqli_result -> fetch_assoc() returns a piece of data in the result set. This data is an associative array. The key is the field name of the database table and the value is in the table. value.

   array(3) {
     ["id"]=>
     string(1) "1"
     ["name"]=>
     string(6) "张三"
     ["price"]=>
     string(7) "1234.60"
   }

2. $mysqli_result -> fetch_row() also returns a piece of data in the result set, which is an index array.

   array(3) {
     [0]=>
     string(1) "1"
     [1]=>
     string(6) "张三"
     [2]=>
     string(7) "1234.60"
   }

3. $mysqli_result = $res -> The array returned by fetch_array() is a combination of an associative array and an index array.

   array(6) {
     [0]=>
     string(1) "1"
     ["id"]=>
     string(1) "1"
     [1]=>
     string(6) "张三"
     ["name"]=>
     string(6) "张三"
     [2]=>
     string(7) "1234.60"
     ["price"]=>
     string(7) "1234.60"
   }

4. $mysqli_result = $res -> fetch_object() returns an object encapsulated with a piece of data. This object uses PHP's built-in standard classes. The fields of the table are attributes of the class.

   object(stdClass)#3 (3) {
     ["id"]=>
     string(1) "1"
     ["name"]=>
     string(6) "张三"
     ["price"]=>
     string(7) "1234.60"
   }

mysqli transaction processing

mysqli provides a total of three ways to start transactions when processing mysql transactions.

• $mySQLi -> query('start transaction');

• $mySQLi -> query('set autocommit = false' );

##$mySQLi -> begin_transaction();

• <?php
      //使用面向对象进行数据库的连接，在创建对象的时候就自动的连接数据
      $mySQLi = new MySQLi(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,3306);
  
  //判断数据库是否连接
  if($mySQLi -> connect_errno){
      die(&#39;连接错误&#39; . $mySQLi -> connect_error);
  }
  //设置字符集
  $mySQLi -> set_charset(&#39;utf8&#39;);
  
  //编写sql语句
  $sql1 = "insert into good values(null,&#39;武松&#39;,2345.7)";
  $sql2 = "update good set price = 3546.67 where id = 2";
  
  //开启事务
  $mySQLi -> query(&#39;start transaction&#39;);
  // $mySQLi -> query(&#39;set autocommit = false&#39;); //第二种方式
  // $mySQLi -> begin_transaction();//第三种方式
  
  
  
  //发送sql语句,因为sql语句是插入和修改语句，返回的结果是一个布尔值。
  $res1 = $mySQLi -> query($sql1);
  $res2 = $mySQLi -> query($sql2);
  
  if($res1 && $res2){
      echo &#39;操作成功&#39;;
      //提交事务。
      $mySQLi -> commit();
  }else{
      echo &#39;操作失败&#39;;
      //进行数据的回滚
      $mySQLi -> rollback();
  }
  
  $mySQLi -> close();

When the statement execution fails, the data can be rolled back.

mysqli executes sql statements in batches

When using PHP to operate the database, sometimes we need to execute multiple sql statements at one time, such as adding users in batches. At this time, if Sending SQL instructions to the MySQL database individually is not efficient. In this case, you can consider using batch execution of SQL statements.

Mysqli syntax for batch execution of sql statements:

$sql = "sql语句1；sql语句2；sql语句3"；
$res = $mysqli -> multi_query();

Of course, there are different return results for batch operations.

If the dml operation statement is executed in batches, the return result is a Boolean value
If the dql (select) operation statement is executed in batches , then the returned result is multiple result sets.

Batch execution of dml statements

:

<?php

    //使用面向对象进行数据库的连接，在创建对象的时候就自动的连接数据
    $mySQLi = new MySQLi(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,3306);

    //判断数据库是否连接
    if($mySQLi -> connect_errno){
        die(&#39;连接错误&#39; . $mySQLi -> connect_error);
    }
    //设置字符集
    $mySQLi -> set_charset(&#39;utf8&#39;);

    $sql = "insert into good values(null,&#39;孙悟空&#39;,1234.8);";
    $sql .= "insert into good values(null,&#39;猪八戒&#39;,4564.3)";

    //进行批量的sql语句执行。
    $res = $mySQLi -> multi_query($sql);

    if($res){
        echo &#39;添加成功&#39;;
    }else{
        echo &#39;添加失败&#39; . $mySQLi -> error;
    }

    $mySQLi -> close();

在进行dml批量操作时，如果有一个语句错误，那么后面的sql语句就不执行了，并且在进行dml批量操作时，返回的布尔值的结果，就是第一条sql语句执行的结果。那么如果第一条语句执行成功，后面的语句执行失败，得到的布尔值也是true。

批量执行dql语句

<?php

    //使用面向对象进行数据库的连接，在创建对象的时候就自动的连接数据
    $mySQLi = new MySQLi(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,3306);

    //判断数据库是否连接
    if($mySQLi -> connect_errno){
        die(&#39;连接错误&#39; . $mySQLi -> connect_error);
    }
    //设置字符集
    $mySQLi -> set_charset(&#39;utf8&#39;);

    $sql = &#39;select id,name from good;&#39;;
    $sql .= &#39;select price from good&#39;;
    echo &#39;<pre class="brush:php;toolbar:false">&#39;;
    //这里返回的记过是一个布尔值。
    if($mySQLi -> multi_query($sql)){
        //得到里面的数据
        do{
            //通过这个函数返回查找的结果集，返回的是一个mysqli_result对象。
            $res = $mySQLi -> store_result();

            while($row = $res -> fetch_assoc()){
                var_dump($row);
            }
            //判断是否还有结果。如果没有退出循环。
            if(!$mySQLi -> more_results()){
                break;
            }
        //相当于一个指针，指向下一个结果。
        }while($mySQLi -> next_result());

    }else{
        echo &#39;执行失败&#39;;
    }
    $mySQLi -> close();

当执行的批量语句是dql语句时，数据库会返回查找的结果。通过mysqli -> store_result()这个方法返回mysqli->result对象。上面的代码中批量执行了两句select，数据库会返回两个结果集，而通过store_result()方法返回的是一条select语句的结果。当显示完数据后通过more_results()方法进行判断是否还有数据。如果没有，跳出循环。有数据的话通过next_result()方法指向下一个结果集。

more_results()方法是判断有没有下一个结果集，但是结果集的指针并不会执行下一个结果集。而next_result()方法是把指针向前挪移一位。

mysqli预处理技术

在PHP操作数据库中使用预处理技术可以大大提高我们的sql语句执行速度。关于sql语句在dbms中的执行时间消耗的步骤如图：

在其中dbms进行分析sql语句的时间大约占20%，而预处理就是把分析sql语句这个步骤省略，从而提高执行sql语句效率。预处理就是通过把我们想传入的参数使用占位符？来表示，通过预处理对象绑定真实的参数。

<?php

    //使用面向对象进行数据库的连接，在创建对象的时候就自动的连接数据
    $mySQLi = new MySQLi(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,3306);

    //判断数据库是否连接
    if($mySQLi -> connect_errno){
        die(&#39;连接错误&#39; . $mySQLi -> connect_error);
    }
    //设置字符集
    $mySQLi -> set_charset(&#39;utf8&#39;);

    $sql = "INSERT INTO good VALUES(?,?,?)";

    //通过prepare()方法返回一个预处理的对象。
    $mysql_stmt = $mySQLi -> prepare($sql);

    $id = 18;
    $name = &#39;松江&#39;;
    $price = 2344.45;
    //绑定参数
    $mysql_stmt -> bind_param(&#39;iss&#39;,$id,$name,$price);
    //通过预处理对象执行。
    if($mysql_stmt -> execute()){
        echo &#39;执行成功&#39;;
    }else{
        echo &#39;执行失败&#39;;
    }

    //当我们还想添加一条数据时，dbms不用分析sql语句。
    $id = 19;
    $name = &#39;武松&#39;;
    $price = 2346.45;
    //绑定参数，
    $mysql_stmt -> bind_param(&#39;iss&#39;,$id,$name,$price);
    //通过预处理对象执行。
    if($mysql_stmt -> execute()){
        echo &#39;执行成功&#39;;
    }else{
        echo &#39;执行失败&#39;;
    }

在上面的代码中，通过mysqli -> prepare()方法得到预处理对象，而sql语句里面的参数是通过占位符？表示。得到预处理对象后通过定义想传递的参数，使用bind_param()方法进行参数的绑定。然后通过execute()方法进行执行，之后如果执行同样的参数，只要把参数定义完之后，进行绑定执行就行了。

bind_param(参数1，参数2):这个方法是绑定参数的方法，里面一共有两个参数，第一个参数是我们绑定参数的类型，我们一般用到三个值：

• i int类型

• d double类型，也就是小数类型

• s 字符串类型  
  第二个参数是对象第一个参数的变量的值。

上面的是插入的时候时候预处理，在dml，dql都可以使用预处理。

DaoMysqli.class.php开发

PHP是面向对象的语言，而在操作数据库时，我们可以把一些功能进行封装，创建成对象。使用DaoMySqli这个封装好的类，可以简化我们的项目，体现面向对象的思想。

DaoMysqli.class这个类的实现：

1. 使用单例模式控制资源，始终只有一个对象。

2. 使用final关键字修饰类防止被继承。

3. 使用魔术方法__clone()，防止克隆。

4. 通过在类里面创建mysqli对象，连接数据库。

5. 通过mysqli在类里面进行数据的增删改查等操作，把操作过程封装起来。

---

单例模式

//把构造函数私有化，在类外部创建不了对象。
    private function __construct($canshu){
        $this -> initMySQLi($canshu);
    }

public static function getInstrance($canshu){
            if(!(self::$daoMysqli instanceof DaoMysqli)){
                self::$daoMysqli = new DaoMysqli($canshu);
            }
            return self::$daoMysqli;
}

把构造函数私有化，在类外面不能创建对象。同时提供一个创建对象的静态方法，在静态里面创建DaoMysqli对象和mysqli对象。

防止继承、克隆

//防止继承。
final class DaoMysqli{
//防止克隆。
private function __clone(){}

创建mysqli对象

//初始化mysqli对象。
        private function initMySQLi($canshu){
            $this -> host = isset($canshu[0]) ? $canshu[0] : '';
            $this -> user = isset($canshu[1]) ? $canshu[1] : '';
            $this -> password = isset($canshu[2]) ? $canshu[2] : '';
            $this -> db_name = isset($canshu[3]) ? $canshu[3] : '';
            //如果端口号没有传入，默认是3306
            //编码默认是utf8。
            $this -> duankou = isset($canshu[4]) ? $canshu[4] : 3306;
            $this -> charset = isset($canshu[5]) ? $canshu[5] : 'utf8';
            if($this -> host == '' || $this -> user == '' || $this -> password == '' || $this -> db_name == ''){
                die('参数不能为空');
            }

            $this -> mySQLi = new MySQLi($this -> host,$this -> user,$this -> password,$this -> db_name,$this -> duankou);
            if($this -> mySQLi -> connect_errno){
                    die('连接错误，错误信息是' . $this -> mySQLi -> connect_error);
            }
            $this -> mySQLi -> set_charset($this -> charset);
        }

在类里面通过调用私有的构造函数进行对象的创建。

通过mysqli对象操作数据库

//dql操作返回一个数组。
        public function mySelect($sql){

            if($res = $this -> mySQLi -> query($sql)){
                $res = $this -> mySQLi -> query($sql);
                $rows = array();
                while($row = $res -> fetch_assoc()){
                    $rows[] = $row;
                }
                return $rows;
            }else{
                die('错误,' . $this -> mySQLi -> error);
            }
        }
        //dml操作。
        public function dml($sql){
            return $this -> mySQLi -> query($sql);
        }

当时dql语句时，可以在方法里面进行处理，直接把数据解析出来，放到一个数组里面进行返回。

源代码

<?php
final class DaoMysqli{
    private static $daoMysqli; //类本身对象

    private $mySQLi; //mysqli对象，在类里面操作数据库。

    private $host; //主机名
    private $user;//用户名
    private $password;//密码
    private $db_name;//数据库名字
    private $duankou; //数据库占用的端口号。
    private $charset; //使用的字符集

    //把构造函数私有化，在类外部创建不了对象。
    private function __construct($canshu){
        $this -> initMySQLi($canshu);
    }

    //初始化mysqli对象。
    private function initMySQLi($canshu){
        $this -> host = isset($canshu[0]) ? $canshu[0] : &#39;&#39;;
        $this -> user = isset($canshu[1]) ? $canshu[1] : &#39;&#39;;
        $this -> password = isset($canshu[2]) ? $canshu[2] : &#39;&#39;;
        $this -> db_name = isset($canshu[3]) ? $canshu[3] : &#39;&#39;;
        //如果端口号没有传入，默认是3306
        //编码默认是utf8。
        $this -> duankou = isset($canshu[4]) ? $canshu[4] : 3306;
        $this -> charset = isset($canshu[5]) ? $canshu[5] : &#39;utf8&#39;;
        if($this -> host == &#39;&#39; || $this -> user == &#39;&#39; || $this -> password == &#39;&#39; || $this -> db_name == &#39;&#39;){
            die(&#39;参数不能为空&#39;);
        }

        $this -> mySQLi = new MySQLi($this -> host,$this -> user,$this -> password,$this -> db_name,$this -> duankou);
        if($this -> mySQLi -> connect_errno){
                die(&#39;连接错误，错误信息是&#39; . $this -> mySQLi -> connect_error);
        }
        $this -> mySQLi -> set_charset($this -> charset);
    }
    //防止克隆。
    private function __clone(){}

    public static function getInstrance($canshu){
        if(!(self::$daoMysqli instanceof DaoMysqli)){
            self::$daoMysqli = new DaoMysqli($canshu);
        }
        return self::$daoMysqli;
    }

    //dql操作返回一个数组。
    public function mySelect($sql){

        if($res = $this -> mySQLi -> query($sql)){
            $res = $this -> mySQLi -> query($sql);
            $rows = array();
            while($row = $res -> fetch_assoc()){
                $rows[] = $row;
            }
            return $rows;
        }else{
            die(&#39;错误,&#39; . $this -> mySQLi -> error);
        }
    }
    //dml操作。
    public function dml($sql){
        return $this -> mySQLi -> query($sql);
    }
}

测试

<?php
    require &#39;./DaoMysqli.php&#39;;
    $canshu = array(&#39;localhost&#39;,&#39;root&#39;,&#39;123456&#39;,&#39;test&#39;,&#39;3306&#39;);
    $dao = DaoMysqli::getInstrance($canshu);
    $sql = "select * from good";
    $res = $dao -> mySelect($sql);
    foreach ($res as $value) {
        var_dump($value);
        echo &#39;<br>&#39;;
    }

参数是通过一个数组传进去的。

总结

PHP操作数据库的方法中很多中，mysqli只是其中的一种，这种操作方式我们更容易理解和掌握，但是mysqli还是有一些不足，我们在PHP开发中有时候使用的数据库并不是mysql数据库，而是别的数据库，这时，mysqli这种方式就显得有些无力，mysqli只是纯粹的操作mysql数据库，对于别的数据库没有办法操作。

The above is the content of the fourteenth PHP basic tutorial on using MySqli to operate the database. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!