Using the Improved MySQL Extension

In Chapter 7, "Using PHP with MySQL," I introduced the standard set of PHP functions used to interact with a MySQL database. These functions have been around for years and will certainly get the job done. But new to PHP as of version 5 is the Improved MySQL Extension functions, designed to take advantage of features added in MySQL 4.1 (they specifically work with MySQL 4.1.3 and later). If your server meets these requirements, you may want to consider using these functions in your PHP applications. Generally speaking, they are both faster and more secure than the basic MySQL functions.

To start, connect to MySQL using the mysqli_connect() function.

 $dbc = mysqli_connect ('localhost',  'username, 'password');

As with the mysql_connect() function, this one returns a resource representing the database connection. This resource will be used as an argument in most of the other mysqli functions. But unlike the mysql_connect() function, mysqli_connect() will let you also specify the database to select:

 $dbc = mysqli_connect ('localhost',  'username, 'password', 'database');

Or you can use the mysqli_select_db() function:

 mysqli_select_db($dbc, 'database');

If a connection problem occurred, you can call the mysqli_connect_error() function, which returns the connection error message.

 mysqli_connect_error();

Note that this function is only for connection errors. The errors resulting from running queries can be reported by the mysqli_error() function:

 mysqli_error($dbc);

Once you've connected to MySQL and selected a database, you run queries using the mysqli_query() function:

 $result = mysqli_query($dbc, $query);

Notice that the syntax of this function places the database connection as the first argument and the query as the second, which is the opposite of the standard mysql_query() function. You'll also notice that whereas the database connection is an optional argument in most of the mysql functions, it's required with the Improved MySQL Extension.

To handle the results returned by a SELECT query, use mysqli_fetch_array() in a while loop:

 while ($row = mysqli_fetch_array  ($result)) {    // Do whatever with $row. }

As with mysql_fetch_array(), you can add a second parameter when calling this function, dictating how the returned array should be indexed. The options are: MYSQLI_NUM, for numeric (starting at 0); MYSQLI_ASSOC, for strings (using the column names); and MYSQLI_BOTH, for both.

You can count the number of returned records with

 $num = mysqli_num_rows($result);

And once you are finished with a query's results, you can release them and close the database connection:

 mysqli_free_result($result); mysqli_close($dbc);

These are the most important of the mysqli_* functions. There is also: mysqli_real_escape_string() for escaping problematic characters; mysqli_affected_rows(), which returns the number of rows affected by an INSERT, UPDATE, or DELETE query; a few functions related to using transactions, which will be used in Chapter 14, "ExampleE-commerce"; and about two or three dozen others, all listed in the PHP manual.

As you can see, for the most part you'll use these functions exactly as you do the standard mysql_* functions. As a quick demonstration of this, I'll write a script that retrieves all of the comments stored in the test database. The specific table was created and the comments were added at the beginning of Chapter 10, "Web Application Security"; if you do not have a populated comments table, read that section first. Also, make sure that you are running PHP 5 or later with MySQL 4.1.3 or later. And if you do not know how to enable mysqli support in PHP, see Appendix A.

To use the Improved MySQL Extension

1.	Create a new PHP script in your text editor or IDE (Script 11.6). <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN "http://www.w3.org/TR/xhtml1/DTD/ xhtml1-transitional.dtd> <html xmlns="http://www.w3.org/1999/ xhtml xml:lang="en" lang="en"> <head> <meta http-equiv="content-type" content="text/html; charset= iso-8859-1 /> <title>View Submitted Comments </title> </head> <body> <?php # Script 11.6 - mysqli.php Script 11.6. The Improved MySQL Extension functions are used here to retrieve and display some records from a database.
2.	Establish a connection to the database. $dbc = @mysqli_connect ('localhost', 'username, 'password', 'test') OR die ('Could not connect to MySQL: ' . mysqli_connect_error() ); This function will attempt to connect to MySQL and select the test database. If it succeeds, the result will be assigned to the `$dbc` variable. If the function call fails, the `die()` function will terminate the execution of the script, printing the connection error in the process (Figure 11.17). Figure 11.17. The `mysqli_connect_error()` function returns any errors that occur specifically while establishing a connection to MySQL.
3.	Define and execute the query. $query = "SELECT name, comment, DATE_FORMAT(date_entered, '%M %D, %Y) FROM comments ORDER BY date_entered DESC; if ($result = @mysqli_query ($dbc, $query)) { The query itself is the same as one you would use if you were working with the standard MySQL functions. This one retrieves all the records from the comments table in descending date entered order, formatting the date in the process. The execution of the query takes place in a conditional, so that I can report on any errors if there is a problem.
4.	Print the query results. if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_array ($result, MYSQLI_NUM)) { echo "<h3>$row[0] ($row[2]) </h3> <p>$row[1]</p><br />"; } } else { echo '<p>There are currently no comments in the database.</p>'; } If at least one record was returned, I'll use a `while` loop to print each record. The loop calls the `mysqli_fetch_array()` function, using the `MYSQLI_NUM` parameter so that I can refer to the returned columns using numeric indexes. If no records were returned, a message stating such is printed.
5.	Complete the page. } else { echo '<p><font color="red"> MySQL Error: ' . mysqli_error ($dbc) . '<br /><br />Query: ' . $query . '</font></p>'; } mysqli_free_result($result); mysqli_close($dbc); ?> </body> </html> The `else` clause is invoked if there was an error in executing the query. For debugging purposes, the MySQL error will be printed along with the query being run (Figure 11.18). Figure 11.18. If a non-connection error occurs, the error is printed out along with the query that was being run. Then the query results are freed and the database connection is closed. These are optional, but good, steps to take.
6.	Save the page as `mysqli.php`, upload to your Web server, and test in your Web browser (Figure 11.19). Figure 11.19. The resulting page, showing all of the submitted comments (actually submitted in Chapter 10).

Tips

You can use the Improved MySQL Extension in a procedural manner using functions, as I do in this example, or as an object, assuming you understand object-oriented programming. See the PHP manual for the OOP syntax.
Another benefit of the Improved MySQL Extension is that the mysqli_multi_query() function lets you execute multiple queries at one time.

To use the Improved MySQL Extension

Script 11.6. The Improved MySQL Extension functions are used here to retrieve and display some records from a database.

Figure 11.17. The mysqli_connect_error() function returns any errors that occur specifically while establishing a connection to MySQL.

Figure 11.18. If a non-connection error occurs, the error is printed out along with the query that was being run.

Figure 11.19. The resulting page, showing all of the submitted comments (actually submitted in Chapter 10).

Figure 11.17. The `mysqli_connect_error()` function returns any errors that occur specifically while establishing a connection to MySQL.