Core Web Application Development with PHP and MySQL

We will spend some time demonstrating how the procedural interfaces to MySQL work since these are what you will mostly see in existing code, and since other database servers do not have an object-oriented interface similar to the mysqli class yet (though their procedural interfaces closely mirror those for MySQL).

The Basics of Procedural Database Interfaces

Earlier in this chapter, we listed a series of events that occur for connecting to a database server, executing a query, obtaining the results, cleaning up, and disconnecting. The mysqli class merged the first three into one operation (connection, authentication, and selection of the database with which to work). We can mirror this sequence of events with these procedural functions:

• Use mysqli_connect to connect to the server (see the next section for other means of connecting), authenticate the user, and select a database.

• Use mysqli_query to execute a query on the server.

• Call mysqli_fetch_assoc (or mysqli_fetch_array or mysqli_fetch_object) to retrieve the data from the server.

• Call mysqli_free_result to release the result set from a query.

• Call mysqli_close to close the connection and end communication with the server.

Our code to list the users in a database could be written using the procedural interface as follows:

<?php $conn = mysqli_connect('localhost', 'mbuser', 'mbuser', 'MessageBoard'); if (mysqli_connect_errno() != 0) { $errno = mysqli_connect_errno(); $errmsg = mysqli_connect_error(); echo "Connect Failed with: ($errno) $errmsg<br/>\n"; exit; } // don't forget to set our query as UTF-8 !!!! @mysqli_query($conn, "SET NAMES 'utf8'"); $query_str = "SELECT * FROM Users"; $result = @mysqli_query($conn, $query_str); if ($result === FALSE) { $errno = mysqli_errno(); $errmsg = mysqli_error(); echo "Connect Failed with: ($errno) $errmsg<br/>\n"; mysqli_close($conn); exit; } else { echo <<<EOM <table border='1'> <tr> <td>User ID</td> <td>Username</td> <td>Full Name</td> <td>Email Address</td> <td>Birthdate</td> </tr> EOM; while (($row_data = @mysqli_fetch_assoc($result)) !== NULL) { echo <<<EOM <tr> <td>{$row_data['user_id']}</td> <td>{$row_data['user_name']}</td> <td>{$row_data['full_name']}</td> <td>{$row_data['user_email']}</td> <td>{$row_data['birthdate']}</td> </tr> EOM; } echo <<<EOTABLE </table> EOTABLE; // clean up result sets when we're done with them! mysqli_free_results($results); } // finally, clean up the connection. mysqli_close($conn); ?>

Similarly, we can call the methods mysqli_insert_id and mysqli_affected_rows to find out the auto-generated ID of the last inserted row or the number of rows affected by the last UPDATE or DELETE queries.

There are procedural methods for working with prepared statements in mysqli, but since they do not have common analogues in the other database servers' interfaces, we will not show them here. Instead, we will continue to work with the object-oriented classes.

Persistent Connections

One of the features that the older procedural interfaces have is persistent connections. In particular, the older mysql extension supports these and many of the other database extensions, such as pg (for PostgreSQL servers), mssql (for Microsoft SQL Server), and oci8 (for recent versions of Oracle).

The theory of how the persistent connections works is as follows:

• You use a function called mysqli_pconnect instead of mysqli_connect. This function has PHP remember your connection for the current web server/PHP process so it just uses the existing one the next time your code asks to create the same connection. As your web server spawns new processes or threads to deal with greater incoming loads, each of these can remember one connection.

• When you call the mysqli_close method, it does not close the connection, but instead keeps the connection open.

• Only when another connection to a different database server is required is the existing one closed and a new connection established.

The reality for accessing MySQL with the new mysqli extension is somewhat different:

• The newer binary protocol used to connect to recent MySQL versions is quite fast.

• If you have multiple web sites running on the same web server and PHP installation (a feature known as virtual servers) or you run code to connect different database servers, PHP can spend time figuring out who has which connection and whether it needs to close and re-create the connection.

• The amount of code required to correctly manage persistent connections in a secure and robust fashion would eat up much of the performance gains obtained by using it.

• The persistent connection code was largely regarded as a massive hack by some developers working on it in the PHP code base.

The final (and controversial) decision was made not to include a mysqli_pconnect method. The mysqli extension does not support persistent connections. However, you should not mourn this too greatly. Even if you are using the older mysql extension or one of the other interfaces that does still support persistent connections, you should be very careful, particularly in an environment where you are sharing the web server with other web applications or connecting to multiple databases. It will be worth a few moments of your time to research what people recommend for the particular extension you have chosen to use.