Developing MySQL Database Applications With PHP Part 1

Test

Make a Connection to the Database

Call the mysqli:init() method of the mysqli object to obtain a database connection handle to use with subsequent mysqli::real_connect(). The init() method creates a database connection object but doesn't establish a connection to the MySQL server. In order to establish the actual database connection, one must call the real_connect() method against the database connection object that was returned by the init() method. Until real_connect() is called, all the calls to the mysqli methods except the mysqli:: options() fail.

Calling mysqli:init() followed by a call to mysqli::real_connect() can be simplified by calling mysqli object's constructor with all the necessary parameters to open a connection to the MySQL server.

The mysqli:: options() method can be used to to set extra connect options like the connection timeout, auto-commit mode, and so on, that can affect the behavior of a database connection.

The following code fragment in the sample PHP script establishes the connection to the MySQL database engine running on the local host on default port 3306, sets the default database to test, disables the auto-commit mode, and sets the connection timeout to 5 seconds.

1. $host="localhost";
   
2. $port=3306;
   
3. $socket="/tmp/mysql.sock";
   
4. $user="root";
   
5. $password="admin";
   
6. $dbname="test";
   
7. 
   
8. $cxn = new mysqli();
   
9. $cxn->init();
   
10. 
    
11. $cxn->options (MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
    
12. $cxn->options (MYSQLI_OPT_CONNECT_TIMEOUT, 5);
    
13. 
    
14. $cxn->real_connect ($host, $user, $password, $dbname, $port, $socket)
    
15.              or die ('Could not connect to the database server' . mysqli_connect_error());

复制代码

Another way to turn on or turn off the auto-commit is by calling the mysqli::autocommit() method with an argument of TRUE or FALSE against the database connection object.

For example:

1. /* turn off auto-commit */
   
2. $cxn->autocommit(FALSE);

复制代码

When the auto-commit is turned off, you must commit or roll back the transactions by explicitly calling mysqli::commit() or mysqli::rollback() methods respectively.

Read the man page for the mysqli::real_connect() method for the default values to the input parameters. Also check the note about the safe mode in the tutorial Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql.

Notes:

• If the host is localhost, then the port number is ignored and 3306 is assumed by default. If the MySQL server is running on any port other than the default port 3306, use the IP address 127.0.0.1 in place of localhost for the $host argument.
• The mysqli extension built with libmysql support does not support persistent connections. Consider using the MySQL native driver for PHP, mysqlnd, if you need the mysqli support for persistent connections. For more information, see the tutorial Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd.
  

Execute the SQL Statement

The method mysqli::real_query() executes a single query against the selected database whose result can then be retrieved using the mysqli::store_result() or mysqli::use_result() functions.

The following code fragment in the sample PHP script executes the SQL query.

1. $query = "SELECT * FROM City";
   
2. $cxn->real_query ($query)

复制代码

To execute multiple queries (also called Multiple Statements), which are concatenated by a semicolon, use the mysqli::multi_query() method. Check the man page of mysqli::multi_query() for examples.

Retrieve the Data From the Result Set

Call mysqli::use_result() to initiate the retrieval of the result set from the last query executed using the mysqli::real_query() function. The method use_result() returns the result as an unbuffered set, meaning that the set is retrieved on an as-needed basis from the server. Unbuffered result sets increase performance for large result sets, but disallow the opportunity to do various things with the result set, such as immediately determine how many rows have been returned by the server, or move to a particular row offset using functions such as mysqli_result::data_seek(). When you are trying to retrieve a very large number of rows, this method might require less memory and produce a faster response time. On the other hand, if a lot of processing on the client side is performed, it might lead to table lockups and prevent other threads from updating the tables from which the data is being fetched. Use your discretion in choosing this method.

Call mysqli::store_result() if you need the ability to move to any row within the result set randomly. Using store_result() returns the result from the last query executed using the mysqli::real_query() function as a buffered set, meaning the entire set is made available for navigation immediately. It is recommended to use store_result() if a lot of processing on the client side is performed because this releases the locks as soon as the result set is transferred to the client. It is good practice to free the memory used by the result of a query by calling the mysqli_result::free() or mysqli_result::close() function after transferring large result sets using the store_result() method.

Either mysqli::use_result() or mysqli::store_result() must be called before the results of a query can be retrieved, and to prevent the next query on that database connection from failing.

The extension mysqli has multiple methods to retrieve the data from a result set. Because the mysqli_result::fetch_assoc() method is used in the sample PHP script, we will focus only on the fetch_assoc() method in this section. For the rest of the mysqli_result::fetch_XX() methods, check the MySQLi_Result class.

The mysqli_result::fetch_assoc() method returns an associative array of strings representing the fetched row in the result set, where each key in the array represents the name of one of the result set's columns, or NULL if there are no more rows in the result set. To retrieve all the rows in the result set, call fetch_assoc() iteratively in a loop.

The following code fragment in the sample PHP script retrieves all the data from the result set and displays it on the standard output.

1. if ($result = $cxn->store_result()) {
   
2. 
   
3.                echo "CityName\n--------\n";
   
4. 
   
5.                            while ($row = $result->fetch_assoc()) {
   
6.                                                       $data1 = $row["CityName"];
   
7.                                                       echo "$data1\n";
   
8.                            } // while
   
9. } // if

复制代码

Clean Up: Release the Resources
Once the required data has been retrieved from the result set, it is a recommended practice to free up the memory that was holding the result set by explicitly calling any of the following methods:

• mysqli_result::free()
• mysqli_result::close()
• mysqli_result::free_result()
  

Sample code frees up the memory associated with the result set by closing the result set at the end.

1. $result->close();

复制代码

If the result set is not closed explicitly by calling any of the three methods mentioned above, all the associated result memory is automatically freed at the end of the script's execution. However if you want to use the existing database connection to execute more queries, make sure to explicitly close the result set by calling the mysqli_result::free_result() method.
Finally, close the database connection by calling the mysqli::close() method. The close() method closes the non-persistent connection to the MySQL server that is associated with the specified connection handle.
The following statement in the sample PHP script closes the database connection.

1. $cxn->close();

复制代码

The LD_DEBUG* lines were added in the output to show that the MySQL client library is being used by the ext/mysqli.
Before concluding the discussion on ext/mysqli, let's look at the support for the prepared statements with a few examples.

Test

Work With Prepared Statements
MySQL 4.1 introduced prepared statements to accomplish the task of executing a query repeatedly, albeit with different parameters in each iteration. Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. However note that even though prepared statements can improve the security, it is still the responsibility of the application developer to guard against security attacks and to sanitize the input before submitting it to the database for processing.

Reduced query parsing due to the prepared statements can lead to significant performance improvements in the MySQL server.
The MySQL client/server protocol supports two methods of sending the database results to the client: as text and as binary. The text protocol always converts the data into strings before sending them across the network, and the server decodes the strings into appropriate data types. Unlike the text protocol, the binary protocol avoids converting the data into strings wherever possible. The binary protocol is used only with the prepared statements. Based on the data being sent over the network, the binary protocol that prepared statements use can reduce the CPU and network overhead by eliminating the encoding and decoding of the strings into the correct data types at the client and the server.

The following statements can be used as prepared statements: CALL, CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. See the latest MySQL Reference Manual to check the support for additional statements.

There are two types of prepared statements: bound-parameter prepared statements, and bound-result prepared statements. Bound-parameter prepared statements allow you to store a query statement on the MySQL server with only the changing data being repeatedly sent to the server and integrated into the query for execution. Similarly, bound-result prepared statements bind the results from a query to a set of PHP variables.

You can use the mysqli_stmt::bind_param() method of ext/mysqli to bind application variables to a prepared statement as parameters and mysqli_stmt::bind_result() to bind the results from a prepared statement to a set of PHP application variables.
The following example uses the bound-parameter as well as bound-result prepared statements to insert a couple of records into the City table of the test database, and then retrieves the result set by binding the PHP variable $city to the CityName field in the SQL statement.

1. bash# cat PHPmysqliPreparedStmtsClient.php
   
2. 
   
3. <?php
   
4. 
   
5.         /* create a new server connection */
   
6.         $cxn = new mysqli("localhost", "root", "admin", "test")
   
7.                             or die ('Could not connect to the database server' . mysqli_connect_error());
   
8. 
   
9.         /* create the query and the corresponding placeholders */
   
10.         $sql = 'INSERT INTO City VALUES(?)';
    
11. 
    
12.         /* create a statement object */
    
13.         $stmt = $cxn->stmt_init();
    
14. 
    
15.         /* prepare the statement for execution */
    
16.         $stmt->prepare($sql);
    
17. 
    
18.         /* bind the parameters */
    
19.         $stmt->bind_param('s', $city);
    
20. 
    
21.         /* assign value to the $city parameter */
    
22.         $city = 'London, UK';
    
23.        
    
24.         /* execute the prepared statement */
    
25.         if ($stmt->execute()) {
    
26.                      echo "\nSuccessfully inserted " . $city . " into the table, City.";
    
27.         }
    
28. 
    
29.         /* assign another value to the $city parameter */
    
30.         $city = 'Paris, France';
    
31.        
    
32.         /* execute the prepared statement one more time*/
    
33.         if ($stmt->execute()) {
    
34.                              echo "\nSuccessfully inserted " . $city . " into the table, City.\n";
    
35.         }
    
36. 
    
37.         /*  reclaim the statement resources */
    
38.         $stmt->close();
    
39. 
    
40.         /* create the query and the corresponding placeholders */
    
41.         $sql = 'SELECT CityName FROM City';
    
42. 
    
43.         /* create a statement object */
    
44.         $stmt = $cxn->stmt_init();
    
45. 
    
46.         /* prepare the statement for execution */
    
47.         $stmt->prepare($sql);
    
48. 
    
49.         /* execute the prepared statement */
    
50.         $stmt->execute();
    
51. 
    
52.         /* store the result */
    
53.             $stmt->store_result();
    
54. 
    
55.         echo "\nRetrieved " . $stmt->num_rows . " row(s).\n";
    
56. 
    
57.         /* bind the result parameters */
    
58.             $stmt->bind_result($city);
    
59. 
    
60.         echo "\nCityName\n--------\n";
    
61. 
    
62.         /* loop through the results and display the data */
    
63.         while($stmt->fetch()) {
    
64.                              echo $city . "\n";
    
65.         }
    
66. 
    
67.         /*  reclaim the statement resources */
    
68.         $stmt->close();
    
69. 
    
70.         /* close the database connection */
    
71.         $cxn->close();
    
72. 
    
73. ?>
    
74. 
    
75. bash# php PHPmysqliPreparedStmtsClient.php
    
76. 
    
77. Successfully inserted London, UK into the table, City.
    
78. Successfully inserted Paris, France into the table, City.
    
79. 
    
80. Retrieved 5 row(s).
    
81. 
    
82. CityName
    
83. --------
    
84. Hyderabad, India
    
85. San Francisco, USA
    
86. Sydney, Australia
    
87. Paris, France
    
88. London, UK

复制代码

Some of the important steps in the above code sample are explained below.
Instantiating the mysqli class via its constructor establishes a usable database connection to the MySQL server.
The mysqli::stmt_init() method initializes a statement and returns an object of type mysqli_stmt for use with mysqli_stmt::prepare().
Regardless of whether you are using the bound-parameter or bound-result prepared statement, first you need to prepare the statement for execution by using the mysqli_stmt::prepare() method.
Call the mysqli_stmt::bind_param() method to bind variable names to corresponding fields.

1. bool mysqli_stmt::bind_param (string types, mixed &var1 [, mixed &varN])

复制代码

where the types parameter represents the data types of each respective variable to follow (represented by &var1, ... &varN in the syntax). At present, four type codes are supported:

• i for all INTEGER types
• d for the DOUBLE and FLOAT types
• b for the BLOB types
• s for all other types including strings
  

For example, if there are two variables and if the first variable is of type string and the second variable is of type integer, use the string "si" as the input for the types parameter.

The method mysqli_stmt::execute() executes a prepared statement.

The method mysqli_stmt::store_result() returns the result set from the prepared statement as a buffered set, meaning the entire set is made available for navigation immediately. Check the notes around the mysqli::store_result() method for the advantages and disadvantages of using buffered result sets.

The method mysqli_stmt::bind_result() binds columns in the prepared statement result set to PHP variables. In the above example, when mysqli_stmt::fetch() is called to fetch the data, the MySQL client/server protocol places the data for the bound columns into the specified variables $city.

Using mysqli_stmt::close() closes a prepared statement, and mysqli::close() closes the database connection.
Check the MySQLi Extension Function Summary page at PHP.net for the rest of the API that isn't discussed in this tutorial.

For More Information

• It is strongly recommended to read the extensive documentation that is available at php.net and dev.mysql.com before choosing an appropriate extension for your application.
• Also check the comparison of features among those three extensions at php.net.
• PHP Manual
• PHP Database Extensions
• MySQL 5.1 Reference Manual
• Beginning PHP and MySQL: From Novice to Professional, W. Jason Gilmore
• Ulf Wendel's Blog: Internet Super Hero
  

Acknowledgments
Ulf Wendel, Sun | MySQL AB