The use of the MYSQLI function library

Overview 1. What is Mysqli

Php-mysql function Library is the PHP operation MySQL Database The most primitive extension library, php-mysqli I for improvement, equivalent to the former enhanced version, also contains the relative advanced function, in addition to increase the security itself, such as can greatly reduce SQL injection and other questions The occurrence of the problem.

2. MySQL is related to the concept of mysqli

(1) MySQL and mysqli are both functional sets of PHP, which are not associated with MySQL database.

(2) before the PHP5 version, the general is to use the PHP MySQL function to drive the MySQL database, such as mysql_query () function, belongs to the process-oriented

(3) After the PHP5 version, added mysqli function, in a sense, it is the MySQL system function enhanced version, more stable more efficient and more secure, and mysql_query () corresponding to the Mysqli_query (), belong to the object-oriented, Manipulating the MySQL database with the object's way

3. The main difference between MySQL and mysqli

(1) MySQL is a non-holding connection function, MySQL each link will open a connected process, so mysqli cost less resources.

(2) Mysqli is a permanent connection function, mysqli multiple runs mysqli will use the same connection process, thus reducing the server overhead. Mysqli encapsulates some of the advanced operations, such as transactions, while encapsulating many of the methods available in the DB operation process.

(3) Mysqli provides object-oriented programming and process-oriented programming, while MySQL can only process-oriented.

For example, the following code is MYSQLI object-oriented programming and process-oriented approach

Object-oriented approach

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?php $mysqli = new Mysqli ("localhost", "My_user", "My_password", "World"); /* Check connection */ if (Mysqli_connect_errno ()) { printf ("Connect failed:%s\n", Mysqli_connect_error ()); Exit (); } printf ("Host Information:%s\n", $mysqli->host_info); /* Close Connection */ $mysqli->close (); ?>

Process oriented approach

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?php $link = Mysqli_connect ("localhost", "My_user", "My_password", "World"); /* Check connection */ if (! $link) { printf ("Connect failed:%s\n", Mysqli_connect_error ()); Exit (); } printf ("Host Information:%s\n", Mysqli_get_host_info ($link)); /* Close Connection */ Mysqli_close ($link); ?>

(4) Mysqli can reduce the risk of overhead and SQL injection by preprocessing statements, while MySQL does not.

To sum up, if you use PHP5, and MySQL version in more than 5.0, I hope you can use mysqli in the future as far as possible using mqsqli, not only efficient, but also more secure, and recommend that you use object-oriented programming.

Here, we also only introduce object-oriented programming methods.

The function uses 1. Connect to the database and get relevant information

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st

<?php $mysqli = @new mysqli ("localhost", "root", "" "," MySQL "); If the connection is wrong if (Mysqli_connect_errno ()) { echo "Connection Database failed:". Mysqli_connect_error (); $mysqli =null; Exit } Get the current character set echo $mysqli->character_set_name (). " <br> "; Get client Information echo $mysqli->get_client_info (). " <br> "; Get MySQL host information echo $mysqli->host_info. " <br> "; Get Server information echo $mysqli->server_info. " <br> "; Get Server version echo $mysqli->server_version. " <br> "; To close a database connection $mysqli->close (); ?>

Run results if the connection is successful

Latin1
Mysqlnd 5.0.10–20111026– $Id: e707c415db32080b3752b232487a435ee0372157 $
localhost via TCP/IP
5.6.12-log
50612

If the connection fails, the result may be

Failed to connect to database: Access denied for user ' root ' @ ' localhost ' (using Password:yes)

Failed to connect to database: Unknown ' Hello '

2. Querying data

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st

<?php $mysqli = @new mysqli ("localhost", "root", "" "," Design "); If the connection is wrong if (Mysqli_connect_errno ()) { echo "Connection Database failed:". Mysqli_connect_error (); $mysqli =null; Exit } Constructing SQL statements $query = "SELECT * from Designer order by ID LIMIT 3"; Execute SQL statement $result = $mysqli->query ($query); Traverse results while ($row = $result->fetch_array (mysqli_both)) { echo "id". $row [' id ']. " <br> "; } Releasing the result set $result->free (); To close a database connection $mysqli->close (); ?>

Run results

1 2 3

id10062 id10063 id10064

It's important to note here that

1	Fetch_array (Mysqli_both)

In this method, there are three parameters, namely Mysqli_both,mysqli_num,mysqli_assoc.

If the parameter is passed in Mysqli_both, the index of the returned array includes both the number and the name.

1 2 3 4 5 6 7 8 9

Array (size=26) 0 = String ' 10062 ' (length=5) ' id ' = = String ' 10062 ' (length=5) 1 = String '?? ' (length=2) ' Name ' = = String '?? ' (length=2) 2 = string ' [email protected] ' (LENGTH=17) ' Email ' + string ' [email protected] ' (LENGTH=17) 3 = String ' 18366119732 ' (length=11) ' Phone ' = String ' 18366119732 ' (length=11)

If the parameter is passed in Mysqli_num, the index of the returned array contains only numbers.

1 2 3 4 5

Array (size=13) 0 = String ' 10062 ' (length=5) 1 = String '?? ' (length=2) 2 = string ' [email protected] ' (LENGTH=17) 3 = String ' 18366119732 ' (length=11)

If the parameter is passed in Mysqli_both, the index of the returned array contains only the name.

1 2 3 4 5

Array (size=13) ' id ' = = String ' 10062 ' (length=5) ' Name ' = = String '?? ' (length=2) ' Email ' + string ' [email protected] ' (LENGTH=17) ' Phone ' = String ' 18366119732 ' (length=11)

In fact, there is an equivalent method Fetch_row (), FETCH_ASSOC ()

The relationship between them is as follows

$result->fetch_row () = mysql_fetch_row () = $result->fetch_array (mysqli_num) = Mysql_fetch_array (Mysqli_num) Returns an indexed array

$result->fetch_assoc () = MYSQL_FETCH_ASSOC () = $result->fetch_array (MYSQLI_ASSOC) = Mysql_fetch_array (MYSQLI_ ASSOC) returns the index column name

If the Fetch_array () method does not pass anything, the Mysqli_both is passed in by default

3. Inserting data

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23

<?php [Email protected] mysqli ("localhost", "root", "" "," Design "); If the connection is wrong if (Mysqli_connect_errno ()) { echo "Connection Database failed:". Mysqli_connect_error (); $mysqli =null; Exit } Inserting data $sql = "INSERT INTO designer (Name,phone) VALUES (' Hello ', ' 18352682923 ')"; Execute INSERT statement $result = $mysqli->query ($sql); If you perform an error if (! $result) { echo "SQL statement error <br>"; echo "ERROR:" $mysqli->errno. "|". $mysqli->error; Exit } If the insert succeeds, the number of rows affected is returned Echo $mysqli->affected_rows; To close a database connection $mysqli->close (); ?>

If the insert succeeds, the result is 1, and if it fails, an error is reported.

4. Modify the Content

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23

<?php [Email protected] mysqli ("localhost", "root", "" "," Design "); If the connection is wrong if (Mysqli_connect_errno ()) { echo "Connection Database failed:". Mysqli_connect_error (); $mysqli =null; Exit } Inserting data $sql = "Update designer set name = ' Hello ' where id = 10062"; Execute INSERT statement $result = $mysqli->query ($sql); If you perform an error if (! $result) { echo "SQL statement error <br>"; echo "ERROR:" $mysqli->errno. "|". $mysqli->error; Exit } If the insert succeeds, the number of rows affected is returned Echo $mysqli->affected_rows; To close a database connection $mysqli->close (); ?>

If the modification succeeds, the number of rows modified is also returned.

5. Preprocessing statements

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30

<?php $mysqli = @new mysqli ("localhost", "root", "" "," Design "); If the connection is wrong if (Mysqli_connect_errno ()) { echo "Connection Database failed:". Mysqli_connect_error (); $mysqli =null; Exit } Prepare a statement to be placed on the server, insert the statement $sql = "INSERT into designer (name, email) values (?,?)"; To generate a preprocessing statement $stmt = $mysqli->prepare ($sql); Give the placeholder symbol a value for each number (binding parameter) I d s B, the first argument is a formatted character, the SS represents two strings, and D represents a number $stmt->bind_param ("ss", $name, $email); Assigning a value to a variable $name = "Mike"; $email = "[email protected]"; Perform $stmt->execute (); Assigning a value to a variable $name = "Larry"; $email = "[email protected]"; Perform $stmt->execute (); Final output echo "Last ID". $stmt->insert_id. " <br> "; echo "affected". $stmt->affected_rows. " Line <br> "; To close a database connection $mysqli->close (); ?>

With the above preprocessing statements, we can also implement data insertion.

So what are the characteristics of preprocessing statements?

1. More efficient, that is, if you execute the same statement multiple times, only the statement data is different, because a statement is prepared on the server side, and then pass the different values to the server, and then let this statement execute. Equivalent to compiling once, using multiple times.

2. Security: You can prevent SQL injection (? PLACEHOLDER) This prevents the injection of abnormal variables.

Therefore, it is not only efficient but also more secure to recommend the use of MYSQLI's preprocessing statements.

Review

The above is the introduction of some methods of mysqli, more detailed content, please check the PHP manual.

Hope to help everyone!

Reprint Please specify:»php advanced features eight of the use of the MYSQLI function library

The use of the MYSQLI function library