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 InterfacesEarlier 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:
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 ConnectionsOne 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:
The reality for accessing MySQL with the new mysqli extension is somewhat different:
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. |