Mysqli extension of PHP5

MySQL I extension of PHP5 is usually used in combination with mysql databases for application systems built using PHP. for a long time, MySQL extension has been accompanied by PHP and MySQL, and provides a channel for interaction between the two. The functions provided by the mysql extension library make it easy for the PHP program to access the MySQL database. However, with the development of MySQL, some new problems have emerged in mysql extension, because mysql extensions cannot support new features of MySQL 4.1 and later versions.

In the face of the lack of mysql extension functions, PHP developers decided to establish a new MySQL extension that supports PHP 5, which is mysqli extension.

1 mysqli introduction

Mysqli extension allows you to use new features of MySQL 4.1 and later versions. compared with mysql extension, mysqli extension has significantly improved in the following aspects.

L compatibility and maintainability: mysqli extension can easily use new MySQL features, so mysqli has higher compatibility with MySQL. Even if more functions are available in the new MySQL version, mysqli extension can be easily supported.

L object-oriented: the mysqli extension has been encapsulated into a class, so that you can use object-oriented programming. Even if you are not familiar with object-oriented programming, mysqli extension also provides a process-oriented programming method for users to choose from.

L speed and security: the mysqli extension execution speed is much faster than the previous version of mysql extension. The mysqli extension supports Password Hashes and verification programs of the new MySQL version, which improves the security of applications.

L pre-prepared statements: pre-prepared statements can improve the performance of repeated statements. mysql extension provides support for pre-prepared statements.

L debugging function: mysqli extension further improves the debugging function and development efficiency.

To use mysqli extension in PHP, add the following settings in the configuration file php. ini:

Extension = php_mysqli.dll

If the preceding settings are already in the configuration file, make sure there is no ";" before extension; otherwise, remove it. The following describes how to use the mysqli extension to access the database.

2. connect and disconnect the server

Mysqli provides two methods for interacting with databases: Object-Oriented and process-oriented. let's take a look at these two methods.

1. object-oriented

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

_ Construct ([string $ host [, string $ username [, string $ passwd [, string $ dbname

[, Int $ port [, string $ socket])

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

L host: The address of the connected server.

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

L passwd: password used to connect to the database. the default value is null.

L dbname: name of the connected database.

L port: TCP port number.

L socket: UNIX socket.

To establish a connection with MySQL, you can use its constructor to instantiate the mysqli class, for example, the following code:

$ Db_host = "localhost"; // address of the connected server

$ Db_user = "root"; // username used to connect to the database

$ Db_psw = "root"; // database connection password

$ Db_name = "sunyang"; // name of the connected database

$ Mysqli = new mysqli ($ db_host, $ db_user, $ db_psw, $ db_name );

Mysqli also provides a member method connect () for connecting to MySQL (). When the instantiation constructor is a null mysqli class, you can use the mysqli object to call the connect () method to connect to MySQL. for example, the following code:

Code 23-26 CD \ codes \ chapter 23rd \ 23.5 \ connect. php

$ Db_host = "localhost"; // address of the connected server

$ Db_user = "root"; // username used to connect to the database

$ Db_psw = "root"; // database connection password

$ Db_name = "sunyang"; // name of the connected database

$ Mysqli = new mysqli ();

$ Mysqli-> connect ($ db_host, $ db_user, $ db_psw, $ db_name );

Close the connection with the MySQL server and call the close () method through the mysqli object. for example:

$ Mysqli-> close ();

2. process-oriented

In the process-oriented approach, 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 very similar to that of the mysql_connect () function in the mysql extension. The following is an example of the usage of the mysqli_connect () function:

Code 23-27 CD \ codes \ chapter 23rd \ 23.5 \ mysqli_connect.php

$ Connection = mysqli_connect ("localhost", "root", "root", "sunyang ");

If ($ connection ){

Echo "database connection successful ";

} Else {

Echo "database connection failed ";

Close the connection with the MySQL server and use the mysqli_close () function. 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 object-oriented methods to interact with MySQL, the process-oriented method used in mysqli extension is not described in detail here. interested readers can refer to the official documentation for relevant information.

In mysqli, run the query () method. the syntax format of this method is as follows:

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

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

L query: the SQL statement sent to the server.

L resultmode: this parameter accepts two values: MYSQLI_STORE_RESULT, which indicates that the result is returned as a buffer set; MYSQLI_USE_RESULT, which indicates that the result is returned as a non-buffer set.

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

Code 23-28 CD \ codes \ chapter 23rd \ 23.5 \ query. 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) {// determines whether the number of rows in the result set is greater than 0

While ($ row = $ result-> fetch_array () {// records in the cyclic output result set

Echo ($ row [0])."
";

Echo ($ row [1])."
";

Echo ($ row [2])."
";

Echo ($ row [3])."
";

Echo "";

}

}

} Else {

Echo "query failed ";

$ Result-> free ();

$ Mysqli-> close ();

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

3-24 query results using the query () method

The query () method is also used to delete (delete), save (insert), and modify (update) records. The following is an example of deleting a record:

Code 23-29 CD \ codes \ chapter 23rd \ 23.5 \ querydelete. 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 "the deletion operation is successful ";

} Else {

Echo "deletion operation failed ";

$ Mysqli-> close ();

The insert and update operations are similar to the delete operations. you can modify the SQL statement accordingly.

4. prepared statements

You can use prepared statements to improve statement reuse performance. in PHP, you can use the prepare () method to query prepared statements and execute prepared statements using the execute () method. PHP has two pre-preparation statements: one is the binding result and the other is the binding parameter.

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 example code of the binding result is as follows:

Code 23-30 CD \ codes \ chapter 23rd \ 23.5 \ bandResult. php

$ Mysqli = new mysqli ("localhost", "root", "root", "sunyang"); // instantiate mysqli

$ Query = "select * from employee ";

$ Result = $ mysqli-> prepare ($ query); // query prepared statements

$ Result-> execute (); // execute the prepared statement

$ Result-> bind_result ($ id, $ number, $ name, $ age); // bind the result

While ($ result-> fetch ()){

Echo $ id;

Echo $ number;

Echo $ name;

Echo $ age;

$ Result-> close (); // close the prepared statement.

$ Mysqli-> close (); // close the connection

When binding the result, the variables in the script must correspond to the fields in the result set one by one. after binding, the variables bound to the result set are retrieved one by one using the fetch () method, finally, close the prepared statement and database connection respectively.

2. bind parameters

The so-called binding parameter is to bind the custom variables in the PHP script to the parameters in the SQL statement (the parameter uses "?" Instead of), bind_param () is used for binding parameters. the syntax format of this method is as follows:

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

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

L types: the data type of the bound variable. four types of characters are accepted, as shown in Table 23-1.

Table 23-1 characters

Character type	Data type
	Integer
	Double
	String
	Blob

The types of characters accepted by the types parameter must correspond to the bound variables one by one.

L 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:

Code 23-31 CD \ codes \ chapter 23rd \ 23.5 \ bandParam. php

$ Mysqli = new mysqli ("localhost", "root", "root", "sunyang"); // instantiate mysqli

$ Query = "insert into employee (emp_number, emp_name, emp_age) values (?,?,?) ";

$ Result = $ mysqli-> prepare ($ query );

$ Result-> bind_param ("ssi", $ number, $ name, $ age); // bind the parameter

$ Number = 'sy0807 ';

$ Name = 'employee7 ';

$ Age = 20;

$ Result-> execute (); // execute the prepared statement

$ Result-> close ();

$ Mysqli-> close ();

You can bind parameters and binding results in a script. the sample code is as follows:

Code 23-32 CD \ codes \ chapter 23rd \ 23.5 \ bandParamResult. 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); // bind the parameter

$ Emp_id = 4;

$ Result-> execute ();

$ Result-> bind_result ($ id, $ number, $ name, $ age); // bind the result

While ($ result-> fetch ()){

Echo $ id ."
";

Echo $ number ."
";

Echo $ name ."
";

Echo $ age ."
";

$ Result-> close ();

$ Mysqli-> close ();

More than 5 queries

Mysqli extension provides the multi_query () method that can execute multiple queries consecutively. the syntax format of this method is as follows:

Bool mysqli_multi_query (mysqli $ link, string $ query)

When executing multiple queries, except the last query statement, each query statement must be separated by a comma. The sample code for executing multiple queries is as follows:

Code 23-33 CD \ codes \ chapter 23rd \ 23.5 \ multi_query.php

$ Mysqli = new mysqli ("localhost", "root", "root", "sunyang"); // instantiate 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 ()){

Echo $ row [0];

Echo"
";

}

$ Result-> close ();

}

If ($ mysqli-> more_results ()){

Echo ("-----------------
"); // Split line between connected queries

}

} While ($ mysqli-> next_result ());

$ Mysqli-> close (); // close the connection

In the above code, the store_result () method is used to obtain a buffer result set. the fetch_row () method is similar to the fetch_array () method and more_results () method () the next_result () method is used to check whether there are more query results from a multi-query. the next_result () method is used to prepare the next query result from a multi-query. The program output result is 23-25.

3-25 execute multiple queries