PHP5 mysqli Tutorials

Mysqli provides an object-oriented and process-oriented approach to interacting with the database, looking at both of these ways. 1. Object OrientedIn an object-oriented manner, MYSQLI is encapsulated into a class that is constructed as follows: __construct ([String $host [, String $username [, String $passwd [, String $dbname [, int $port [, String $socket]] []] The parameters involved in the above syntax are described below. L Host: The server address of the connection. L Username: The user name of the connection database, the default value is the user name of the server process owner. L passwd: The password of the connection database, the default value is empty. L dbname: The database name of the connection. L PORT:TCP Port number. L Socket:unix domain socket. To establish a connection to MySQL, you can instantiate the Mysqli class by its construction method, such as the following code:

<?php $db _host= "localhost";                                        //Connected server address $db _user= "root";                                               //the user name of the connection database $db _psw= "root";                                              //Connection Database Password $db _name= "Sunyang";                                        //the database name of the connection $mysqli =new mysqli ($db _host, $db _user, $db _psw, $db _name);?>

Mysqli also provides a member method for connecting MySQL to connect (). When instantiating a mysqli class with an empty construction method, calling the Connect () method with the Mysqli object also connects to MySQL, for example, the following code:

<?php $db _host= "localhost";                                        //Connected server address $db _user= "root";                                               //the user name of the connection database $db _psw= "root";                                              //Connection Database Password $db _name= "Sunyang";                                        //the database name of the connection $mysqli =new mysqli (); $mysqli->connect ($db _host, $db _user, $db _psw, $db _name);?>

Close the connection to the MySQL server by calling the close () method with the Mysqli object, for example: $mysqli->close (); 2. Process orientedIn a process-oriented manner, the MYSQLI extension provides the function mysqli_connect () to establish a connection to MySQL, which has the following syntax format: mysqli mysqli_connect ([String $host [, String $ username [, String $passwd [, String $dbname [, int $port [, String $socket]]]) mysq The use of the Li_connect () function is very similar to the mysql_connect () function usage in the MySQL extension, and the following is an example of the use of the Mysqli_connect () function:

<?php $connection = Mysqli_connect ("localhost", "root", "root", "Sunyang"), if ($connection) {echo "Database connection Success "; }else {echo "database connection Failed";}?>

Close the connection to the MySQL server using the Mysqli_close () function, for example: Mysqli_close (); 3 accessing data using MysqliThe use of MYSQLI Access data also includes object-oriented and process-oriented methods, in this section we only discuss how to use object-oriented approach to MySQL interaction, about the mysqli extension using the process-oriented approach is no longer detailed here, interested readers can refer to the official documents to obtain relevant information. In Mysqli, execute the query using the query () method, which has the following syntax: Mixed query (string $query [, int $resultmode]) The parameters that are involved in the above syntax are described below. L Query: The SQL statement sent to the server. L Resultmode: This parameter accepts two values, one is Mysqli_store_result, which indicates that the result is returned as a buffered collection, and the other is Mysqli_use_result, which indicates that the result is returned as a non-buffered collection. The following is an example of executing a query using the query () method:

<?php $mysqli =new mysqli ("localhost", "root", "root", "Sunyang")     //instantiate mysqli $query = " SELECT * FROM employee "; $result = $mysqli->query ($query); if ($result) {        if ($result->num_rows>0) {                                            //determine if the number of rows in the result set is greater than 0                  while ($row = $result->fetch_array ()) {                      //records in the result set of the loop output                           echo ($row [0]). " <br> ";                          echo ($row [1]). " <br> ";                          echo ($row [2]). " <br> ";                          echo ($row [3]). " <br> ";                          echo "In the preceding code, Num_rows is a property of the result set, returning the number of rows in the result sets. Method Fetch_array () places the records in the result set into an array and returns them.   Finally, use the free () method to dispose of the memory in the result set and close the database connection using the close () method. The Delete record (delete), save record (insert), and modify record (update) operations are also performed using the query () method, the following is an example of deleting a record:

<?php $mysqli =new mysqli ("localhost", "root", "root", "Sunyang"); Instantiate mysqli $query = "Delete from employee where emp_id=2"; $result = $mysqli->query ($query); if ($result) {echo "delete operation successfully executed";} else {echo "delete operation failed";} $mysqli->close ();?>

Saving a record (insert), modifying the record (update) is similar to deleting a record (delete), and modifying the SQL statement accordingly. 4 Pre-prepared statementsUsing prepared statements can improve the performance of reused statements, in PHP, using the Prepare () method to prepare a statement query, and execute the prepared statement using the Execute () method. PHP has two pre-prepared statements: one is a binding result and the other is a binding parameter. (1). Binding ResultsThe so-called binding result is to bind the custom variables in the PHP script to the corresponding fields in the result set, which represent the records being queried, and the sample code for the binding result is as follows:

<?php $mysqli =new mysqli ("localhost", "root", "root", "Sunyang")     //instantiate mysqli $query = " SELECT * FROM employee "; $result = $mysqli->prepare ($query);                                  //pre-prepared statement query $result->execute ();                                                     //Execute prepared statements $result->bind_result ($id, $number, $name, $age);                 //binding result while ($ Result->fetch ()) {        echo $id;          echo $number;         echo $name;         echo $age; } $result->close ();                                                        //closing prepared Statements $mysqli->close ();                                                      //Closing the connection?>

When binding the result, the variables in the script correspond to field one by one in the result set, and after binding, the binding is taken out of the variable one by one in the result set by the fetch () method, and the prepared statement and the database connection are closed separately. (2). Binding ParametersThe so-called binding parameter is to bind a custom variable in a PHP script to a parameter in an SQL statement (parameter use "?). instead), the binding parameter uses the Bind_param () method, which has the following syntax format: BOOL Bind_param (String $types, Mixed & $var 1 [, Mixed &$ ...] The parameters involved in the above syntax are described below. L Types: The data type of the bound variable, which accepts a variety of characters including 4, as shown in the table.

Character type	The data type represented
I	Integer
D	Double
S	String
B	Blob

The parameter types accepts the kind of character and the bound variable needs to correspond to one by one.   L&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;VAR1: The number of bound variables must be consistent with the number of parameters in the SQL statement. The sample code for the binding parameter is as follows:

<?php $mysqli =new mysqli ("localhost", "root", "root", "Sunyang");             //instantiation of mysqli $query = "INSERT INTO employee (emp_number,emp_name,emp_age) VALUES (?,?,?) "; $result = $mysqli->prepare ($query); $result->bind_param ("SSI", $number, $name, $age);                        //binding parameter $number = ' sy0807 '; $name = ' Employee7 '; $age = 20; $result->execute ();                                                     &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;//executes the prepared statement $result->close (); $mysqli->close ();?>

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

<?php $mysqli =new mysqli ("localhost", "root", "root", "Sunyang")     //instantiate mysqli $query = " SELECT * FROM employee WHERE emp_id <? "; $result = $mysqli->prepare ($query); $result->bind_param ("i", $emp _id);                                  //binding parameters $emp _id=4; $result->execute (); $result->bind_result ($id, $number, $name, $age);                 //binding result while ($ Result->fetch ()) {        echo $id. " <br> ";         echo $number. " <br> ";         echo $name. " <br> ";         echo $age. " <br> "; } $result->close (); $mysqli->close ();?>

more than 5 queriesThe mysqli extension provides a multi_query () method that executes multiple queries consecutively, with the syntax format of the following: bool Mysqli_multi_query (mysqli $link, string $query) when executing multiple queries, In addition to the last query statement, use ";" between each query statement. Separate. The sample code to execute multiple queries is as follows:

$mysqli =new mysqli ("localhost", "root", "root", "Sunyang");             //instantiation of mysqli $query = "Select Emp_name from Employee;"; $query. = "Select Dep_name from Depment"; if ($mysqli->multi_query ($query)) {                                        //Execute multiple queries         do {                 if ($result = $ Mysqli->store_result ()) {                         while ($row = $result->fetch_row ()) {                    & NBsP;            echo $row [0];                                  echo "<br>";                          }                           $result->close ();                 }                  if ($mysqli->more_ Results ()) {                         echo ("-----------------<br> ");                 //a split line between queries                 }          } while ($mysqli->next_result ()); } $mysqli->close ();//close Connection?>

In the preceding code, the Store_result () method is used to obtain a buffered result set; the Fetch_row () method acts like the Fetch_array () method; the More_results () method is used to check for more query results from a multiple query ; the Next_result () method is used to prepare the next query result from a multiple query.

PHP5 mysqli Tutorials