PHP Basic Tutorial 14 using the MYSQLI operations database

Objective

In the actual development, PHP will be used with the database, because in the background need to have too much data to save, and the database is a good place to save data, we use PHP development database is a relational database MySQL, and PHP and MySQL database only connection, We can operate the database through PHP code.

Mysqli

PHP development is inseparable from the database, but in PHP can be connected through the MYSQLI database. However, mysqli can only connect to the MySQL database. At the same time, Mysqli is an object-oriented technology.

Features of Mysqli:

• The efficiency is improved, the stability is strong.

• Manipulate the database.

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

To use the Mysqli feature in PHP, you need to load the Php_mysql.dll dynamic connection file in php.ini.

Operation Flow

1. Create a database in MySQL as an action object.

2. Open the PHP extension library

3. Create an Mysqli object

   $mysql = new Mysqli (host, account, password, database, port number);

   There are several parameters inside.

4. Set character sets

   $mysql-Set_charset (' UTF8 ');

5. Write the SQL statement and execute it. This SQL statement can be a DML,DQL statement

   $mysql, query ($sql);

6. There are four ways to fetch the retrieved results from the page (Assoc, row, object, array) We generally use assoc this way. However, a Boolean value is returned if it is a DML statement.

   $res-Fetch_assoc ();

7. Releases the result set. Close the connection.

   $res, Free (), $mysqli, close ();

When we are inserting, deleting, modifying (DML), we return a Boolean value, but we do not know if there is any change in it. Can be judged by the properties inside $mysqli-affected_rows,mysqli, the result of which is the number of rows that the SQL statement affects on the data table.

<?php    //Use object-oriented database connection, automatically connect data when creating object    $mySQLi = new mysqli (' localhost ', ' root ', ' 123456 ', ' Test ', 3306);    Determine if the database is connected    if ($mySQLi-Connect_errno) {die        (' connection error '. $mySQLi, Connect_error);    }    Sets the character set    $mySQLi, Set_charset (' UTF8 ');    Write the SQL statement and execute    the $sql = "SELECT * from good";    Sends the SQL statement and executes, if it is a SELECT statement, returns an object, and the other returns a Boolean.    $res = Query ($sql)    , $mySQLi Echo ' <pre> ';    Use the FETCH_ASSOC () inside the $res object to take out the data.    while ($row = $res->fetch_assoc ()) {    //  var_dump ($row);    }    //Using the Fetch_row () method    //while ($row = $res, Fetch_row ()) {    //  var_dump ($row);    }    //using 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 code above is a concrete implementation of using MYSQLI. Mysqli is written using object-oriented thinking. About the method.

• $mySQLi-Connect_errno Returns the last connection error of the connection, if return 0 is successful, the connection fails to return 0

• Connect_error-$mySQLi Returns the cause of the connection error.

• $mySQLi-Set_charset (' UTF8 '); Sets the character set in which the parameters are written according to their own circumstances.

• SQL) Use this method to pass the SQL statement to the database execution when you have finished writing an SQL statement. And depending on the type of SQL statement, returning a different result is a Mysqli_result object

• The Mysqli_result object represents the result set obtained from a database query. That is, the result of the SQL query being returned from the database. Get the results inside the Mysqli/_result object provides four ways that they have different differences.

2. $mysqli _result-FETCH_ASSOC () returns a piece of data in the result set, which is an associative array, the key is the field name of the database table, and the value is the value inside the table.

   Array (3) {  ["id"]=>  string (1) "1"  ["Name"]=>  string (6) "Zhang San"  ["Price"]=>  string (7 ) "1234.60"}

4. $mysqli _result-Fetch_row () returns a single data in the result set, which is an indexed array.

   Array (3) {  [0]=>  string (1) "1"  [1]=>  string (6) "Zhang San"  [2]=>  string (7) "1234.60"}

6. $mysqli _result = Fetch_array (), an array of associative arrays and indexed array combinations.

   Array (6) {  [0]=>  string (1) "1"  ["id"]=>  string (1) "1"  [1]=>  string (6) "Zhang San"  ["Name"]=>  string (6) "Zhang San"  [2]=>  string (7) "1234.60"  ["Price"]=>  string (7 ) "1234.60"}

8. $mysqli _result = $res, Fetch_object () returns an object that has a piece of data encapsulated in it. This object is a built-in standard class that uses PHP. The table field is a property of the class.

   Object (StdClass) #3 (3) {  ["id"]=>  string (1) "1"  ["Name"]=>  string (6) "Zhang San"  ["Price"] = =  String (7) "1234.60"}

Transaction processing for MYSQLI

There are three ways to open a transaction in the mysqli process for MySQL.

• $mySQLi, Query (' Start transaction ');

• $mySQLi, Query (' Set autocommit = False ');

• $mySQLi-Begin_transaction ();

  <?php    //Use object-oriented database connection, automatically connect data when creating objects    $mySQLi = new mysqli (' localhost ', ' root ', ' 123456 ', ' Test ', 3306) ;//Determine if the database is connected to if ($mySQLi-Connect_errno) {die    (' connection error '. $mySQLi, connect_error);} Set the character set $mysqli, Set_charset (' UTF8 ');//write SQL statement $SQL1 = "INSERT into good values (null, ' Wu Song ', 2345.7)"; $sql 2 = "Update Good set price = 3546.67 where id = 2 ";//Open transaction $mysqli, query (' Start transaction ');//$mySQLi query (' Set Autocomm it = False '); The second way//$mySQLi-begin_transaction ();//The Third Way//The SQL statement is sent because the SQL statement is an INSERT and modify statement, and the result returned is a Boolean value. $res 1 = $mySQLi, query ($sql 1), $res 2 = $mySQLi, query ($sql 2), if ($res 1 && $res 2) {    echo ' operation succeeded ';    Commits the transaction.    $mySQLi, Commit ();} else{    Echo ' operation failed ';    Rollback of data $mySQLi-    rollback ();} $mySQLi-Close ();

The data can be rolled back when the statement execution fails.

mysqli Bulk execution of SQL statements

When using PHP to manipulate the database, sometimes we need to execute multiple SQL statements at once, such as bulk increase the user, when a single single to the MySQL database to send SQL instructions, the efficiency is not high, you can consider the use of bulk execution of SQL statements.

MYSQLI syntax for bulk execution of SQL statements:

$sql = "SQL statement 1;sql statement 2;SQL statement 3"; $res = $mysqli, Multi_query ();

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

1. If a DML action statement is executed in bulk, the return result is a Boolean value

2. If the DQL (SELECT) Action statement is executed in bulk, the result is multiple result sets.

batch execution of DML statements :

<?php    //Use object-oriented database connection, automatically connect data when creating object    $mySQLi = new mysqli (' localhost ', ' root ', ' 123456 ', ' Test ', 3306);    Determine if the database is connected    if ($mySQLi-Connect_errno) {die        (' connection error '. $mySQLi, Connect_error);    }    Sets the character set    $mySQLi, Set_charset (' UTF8 ');    $sql = "INSERT into good values (null, ' Monkey King ', 1234.8);";    $sql. = "INSERT into good values (null, ' pig ', 4564.3)";    Perform a batch of SQL statement execution.    $res = $mySQLi-Multi_query ($sql);    if ($res) {        echo ' add success ';    } else{        Echo ' Add failed '. $mySQLi error;    }    $mySQLi-Close ();

In the case of a DML bulk operation, if there is a statement error, the subsequent SQL statement is not executed, and the result of the Boolean value returned when the DML bulk operation is performed is the result of the first SQL statement execution. Then if the first statement executes successfully, the subsequent statement execution fails, and the resulting Boolean value is also true.

Bulk execution of DQL statements

<?php    //Use object-oriented database connection, automatically connect data when creating object    $mySQLi = new mysqli (' localhost ', ' root ', ' 123456 ', ' Test ', 3306);    Determine if the database is connected    if ($mySQLi-Connect_errno) {die        (' connection error '. $mySQLi, Connect_error);    }    Sets the character set    $mySQLi, Set_charset (' UTF8 ');    $sql = ' Select Id,name from good; ';    $sql. = ' Select price from good ';    Echo ' <pre> ';    The demerit returned here is a Boolean value.    if ($mySQLi, Multi_query ($sql)) {        //Get the data inside        do{//            Returns the result set of the lookup by this function and returns a Mysqli_result object.            $res = $mySQLi-Store_result ();            while ($row = $res, Fetch_assoc ()) {                var_dump ($row);            }            Determine if there is a result. If the loop is not exited.            if (! $mySQLi-More_results ()) {break                ;            }        Corresponds to a pointer that points to the next result.        }while ($mySQLi-Next_result ());    } else{        echo ' execution failed ';    }    $mySQLi-Close ();

When you execute a bulk statement that is a DQL statement, the database returns the results of the lookup. The Mysqli->result object is returned by this method, Mysqli-Store_result (). In the code above, two select batches are executed, the database returns two result sets, and the Store_result () method returns the result of a SELECT statement. When the data is displayed, the More_results () method is used to determine if there is any data. If not, jump out of the loop. If there is data, point to the next result set through the Next_result () method.

The More_results () method is to determine if there is no next result set, but a pointer to the result set does not execute the next result set. The Next_result () method is to move the pointer forward one bit.

MYSQLI pretreatment Technology

Using preprocessing techniques in the PHP operations database can greatly improve the execution speed of our SQL statements. Steps for the execution time consumption of SQL statements in the DBMS

In which the DBMS takes approximately 20% of the time to parse the SQL statement , and preprocessing is to omit the parse SQL statement, thereby improving the efficiency of the SQL statement execution. Preprocessing is by using placeholders for the parameters we want to pass in? To indicate that a real parameter is bound by a preprocessed object.

<?php    //Use object-oriented database connection, automatically connect data when creating object    $mySQLi = new mysqli (' localhost ', ' root ', ' 123456 ', ' Test ', 3306);    Determine if the database is connected    if ($mySQLi-Connect_errno) {die        (' connection error '. $mySQLi, Connect_error);    }    Sets the character set    $mySQLi, Set_charset (' UTF8 ');    $sql = "INSERT into good VALUES (?,?,?)";    Returns a Preprocessed object through the Prepare () method.    $mysql _stmt = $mySQLi, prepare ($sql);    $id =;    $name = ' Songjiang ';    $price = 2344.45;    Binding parameters    $mysql _stmt-bind_param (' ISS ', $id, $name, $price);    Executed by preprocessing the object.    if ($mysql _stmt, execute ()) {        echo ' executes successfully ';    } else{        echo ' execution failed ';    }    When we also want to add a piece of data, the DBMS does not have to parse the SQL statement.    $id = +;    $name = ' Wu Song ';    $price = 2346.45;    Binding parameters,    $mysql _stmt-bind_param (' ISS ', $id, $name, $price);    Executed by preprocessing the object.    if ($mysql _stmt, execute ()) {        echo ' executes successfully ';    } else{        echo ' execution failed ';    }

In the above code, the preprocessing object is obtained through the mysqli-prepare () method, and the parameters inside the SQL statement are through placeholders? Said. After you get the preprocessing object, you use the Bind_param () method to bind the parameters by defining the parameters that you want to pass. It is then executed through the Execute () method, and then, if the same parameters are executed, the binding executes as soon as the parameters are defined.

Bind_param (parameter 1, parameter 2): This method is a method of binding parameters, there is a total of two parameters, the first parameter is the type of our binding parameters, we generally use to three values:

• I int type

• D double type, which is the decimal type

• s string type
  The second parameter is the value of the variable for the first argument of the object.

Above is the time of the insertion of the preprocessing, in DML,DQL can use preprocessing.

DaoMysqli.class.php Development

PHP is an object-oriented language, and in the operation of the database, we can put some functions to encapsulate, create a built object. Using Daomysqli, the packaged class, simplifies our project and embodies the idea of object-oriented.

Daomysqli.class implementation of this class:

1. Using singleton mode to control resources, there is always only one object.

2. Use the final keyword to decorate the class to prevent it from being inherited.

3. Use the Magic Method __clone () to prevent cloning.

4. Connect to the database by creating a Mysqli object inside the class.

5. Through the mysqli in the class inside The data deletion and modification of the operation, the operation process encapsulation.

Single-Case mode

Privatize the constructor and create no objects outside the class.    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;}

The constructor is privatized and objects cannot be created outside the class. Also provides a static method for creating objects, creating Daomysqli objects and Mysqli objects in static.

Prevent inheritance, cloning

Prevent inheritance. Final class daomysqli{//prevents cloning. Private Function __clone () {}

Create a Mysqli object

Initializes the Mysqli object.            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]: ";            If the port number is not passed in, the default is 3306//encoding is UTF8 by default. $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 (' parameter cannot be null '); } $this mysqli = new Mysqli ($this, host, $this, user, $this-password, $this-db_name, $t            His-Duankou);             if ($this-mysqli-Connect_errno) {die (' connection error, error message is '. Connect_error, Mysqli, $this);           } Mysqli, Set_charset, $this, ($this, CharSet); }

Inside a class, the object is created by invoking a private constructor.

Manipulating databases with Mysqli objects

The DQL operation returns an array. Public        function Myselect ($sql) {            if ($res = $this, mysqli, Query ($sql)) {                $res = $this, mysqli-& Gt Query ($sql);                $rows = Array ();                while ($row = $res, Fetch_assoc ()) {                    $rows [] = $row;                }                return $rows;            } else{die                (' Error, '. $this, mysqli);            }        }        DML operations. Public        function DML ($sql) {            return $this, mysqli, query ($sql);        }

At that time DQL statement, can be processed inside the method, directly parse the data out, put in an array to return.

Source

<?phpfinal class daomysqli{private static $daoMysqli;//class itself object private $mySQLi;//mysqli object, manipulate database in class. Private $host; Host name private $user;//user name private $password;//password private $db _name;//database name private $duankou;    The port number occupied by the database. Private $charset;    The character set used//The constructor is privatized, and objects cannot be created outside the class.    Private function __construct ($canshu) {$this, initmysqli ($canshu);    }//Initialize the Mysqli object.        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]: ";        If the port number is not passed in, the default is 3306//encoding is UTF8 by default. $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 (' parameter cannot be null '); } $this mysqli = new Mysqli ($this, host, $this, user, $this-password, $this-db_name, $this        Duankou);        if ($this-mysqli-Connect_errno) {die (' connection error, error message is '. Connect_error, Mysqli, $this);    } mysqli, Set_charset, $this, ($this, CharSet);    }//Prevent cloning. Private Function __clone () {} public static function Getinstrance ($canshu) {if (! (        Self:: $daoMysqli instanceof Daomysqli)} {self:: $daoMysqli = new Daomysqli ($canshu);    } return Self:: $daoMysqli;    The//DQL operation returns an array. Public Function Myselect ($sql) {if ($res = $this, mysqli, Query ($sql)) {$res = $this, mysq            Query ($sql), Li            $rows = Array ();            while ($row = $res, Fetch_assoc ()) {$rows [] = $row;        } return $rows; }else{die (' Error, '. $this, MYSQLi-error);    }}//DML operation.    Public function DML ($sql) {return $this, mysqli, query ($sql); }}

Test

<?php    require './daomysqli.php ';    $canshu = Array (' localhost ', ' root ', ' 123456 ', ' Test ', ' 3306 ');    $dao = Daomysqli::getinstrance ($canshu);    $sql = "SELECT * from good";    $res = $dao-Myselect ($sql);    foreach ($res as $value) {        var_dump ($value);        Echo ' <br> ';    }

The arguments are passed in an array.

Summarize

PHP operation of the database in many ways, Mysqli is just one of them, this mode of operation we are easier to understand and grasp, but mysqli still have some shortcomings, we in PHP development sometimes used in the database is not the MySQL database, but other databases, at this time, Mysqli this way seems a little weak, mysqli just pure operation MySQL database, for other database no way to operate.

The above is the PHP Basic Tutorial 14 Use MYSQLI operation database content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

