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

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

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

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

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

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

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

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

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

