PHP 5 mysqli Extension

In general, applications built with PHP are paired with the MySQL database, and for a long time the MySQL extension has been accompanied by PHP and MySQL, and provides a channel for interaction between them. The MySQL extension library provides related functions that make it easy for PHP programs to access the MySQL database, but with the development of MySQL, there are some new problems with MySQL extensions because MySQL extensions cannot support the new features of MySQL 4.1 and later.

Faced with the lack of MySQL extensions, PHP developers decided to build a new MySQL extension that supports PHP 5, which is the mysqli extension.

1 mysqli Introduction

The mysqli extension allows users to take advantage of the new features of MySQL 4.1 and its later versions, and the mysqli extension has been significantly improved in the following areas compared to MySQL extensions.

L Compatibility and maintainability: The mysqli extension makes it easy to use MySQL's new features, so mysqli has higher compatibility with MySQL. Even if the new version of MySQL comes up with more features, the mysqli extension can be easily supported.

L Object-oriented: The mysqli extension is encapsulated in a class that can be programmed using object-oriented methods. Even with object-oriented knowledge, the MYSQLI extension provides process-oriented programming for users to choose from.

L Speed and Security: The mysqli extension executes much faster than the previous version of MySQL extension. The mysqli extension supports MySQL's new version of password hashing (Password hashes) and validators, which improves the security of the application.

L Prepared statements: pre-prepared statements improve the performance of reused statements, and the MYSQLI extension provides support for pre-prepared statements.

L Debug Function: Mysqli extension further improved the debugging function, improve the development efficiency.

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

Extension=php_mysqli.dll

If the above settings are already in the configuration file, make sure that there is no ";" in front of the extension, otherwise remove it. Here's how to use the mysqli extension to access the database.

2 Connecting and disconnecting servers

MYSQLI provides an object-oriented and process-oriented approach to interacting with the database, looking at both of these ways.

1. Object oriented

In the object-oriented approach, 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:

$db _host= "localhost"; The server address of the connection

$db _user= "root"; User name of the connection database

$db _psw= "root"; Password to connect to the database

$db _name= "Sunyang"; 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:

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

$db _host= "localhost"; The server address of the connection

$db _user= "root"; User name of the connection database

$db _psw= "root"; Password to connect to the database

$db _name= "Sunyang"; 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 oriented

In a process-oriented manner, the MYSQLI extension provides the function mysqli_connect () to establish a connection to MySQL, which has the following syntax:

Mysqli mysqli_connect ([String $host [, String $username [, String $passwd

[, String $dbname [, int $port [, String $socket]] []] )

The use of the Mysqli_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:

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

$connection = Mysqli_connect ("localhost", "root", "root", "Sunyang");

if ($connection) {

echo "Database connection succeeded";

}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 Mysqli

The 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 format:

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

The parameters 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:

Code 23-28 CD \codes\ 23rd Chapter \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 ()) {//Loop output record in 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 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 output of the above code is shown in 23-24.

Figure 23-24 Query results using the query () 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:

Code 23-29 CD \codes\ 23rd Chapter \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 "Delete operation executed successfully";

}else {

echo "Delete operation failed to execute";

$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 statements

Using 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 results

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

Code 23-30 CD \codes\ 23rd Chapter \23.5\bandresult.php

$mysqli =new mysqli ("localhost", "root", "root", "Sunyang"); Instantiate mysqli

$query = "SELECT * FROM Employee";

$result = $mysqli->prepare ($query); Making a pre-prepared statement query

$result->execute (); Execute prepared statements

$result->bind_result ($id, $number, $name, $age); Binding results

while ($result->fetch ()) {

echo $id;

Echo $number;

Echo $name;

Echo $age;

$result->close (); Closing prepared statements

$mysqli->close (); Close 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 parameters

The 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 table 23-1.

Table 23-1 Character Descriptions

Character type	The data type represented
	Integer
	Double
	String
	Blob

The parameter types accepts the kind of character and the bound variable needs to correspond to 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 the binding parameter is as follows:

Code 23-31 CD \codes\ 23rd Chapter \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); Binding parameters

$number = ' sy0807 ';

$name = ' Employee7 ';

$age = 20;

$result->execute (); Execute prepared statements

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

Code 23-32 CD \codes\ 23rd Chapter \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); Binding parameters

$emp _id=4;

$result->execute ();

$result->bind_result ($id, $number, $name, $age); Binding results

while ($result->fetch ()) {

echo $id. "
";

echo $number. "
";

echo $name. "
";

echo $age. "
";

$result->close ();

$mysqli->close ();

More than 5 queries

The mysqli extension provides a multi_query () method that can execute multiple queries consecutively, with the syntax of the following format:

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:

Code 23-33 CD \codes\ 23rd Chapter \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 ("-----------------
"); 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. The output of the program is shown in 23-25.

Figure 23-25 Executing multiple queries

