3.5 Before and After: Querying and Retrieving Data with Prepared Statements

One way to speed up MySQL is to use prepared statements. Every time you make an SQL query, MySQL parses the request, checks its validity, and executes it. While MySQL does this efficiently , prepared statements make this process even faster.

A prepared statement is a way to tell MySQL what your query will look like before you actually execute it. This description doesn't contain the exact query; it's more like a query template. Most of the query is hardcoded, but there are placeholders where you want to customize the information.

You pass this template to MySQL. It parses and validates the query, and returns a statement handle. You then use that handle to execute the request.

In contrast to a traditional query, when you execute this type of request, there's no need to parse the SQL. As a result, MySQL executes the query faster. If you make the same query more than once, it's faster to use a prepared statement than a direct query. Additionally, prepared statements automatically escape quotes, so you don't need to worry about stray characters . This is a big benefit that makes prepared statements worthwhile even for single queries.

Prepared statements also let you control data retrieval. There's no longer a need to first retrieve a row into an array and then assign each element to an individual variable. You can instruct MySQL to place each piece of data directly into a variable.

3.5.1 PHP 4: Traditional Input Queries

Example 3-1 demonstrates the traditional way to insert data into a table.

Example 3-1. Executing a traditional query

 // User-entered data $username = 'rasmus'; $password = 'z.8cMpdFbNAPw'; $zipcode  = 94088; // Escape data $username = mysqli_real_escape_string($db, $username); $password = mysqli_real_escape_string($db, $password); $zipcode  = intval($zipcode); // Create SQL query $sql = "INSERT INTO users VALUES ('$username', '$password', $zipcode)"; // Make SQL query mysqli_query($db, $sql) or die('Error'); 

There are three distinct parts of the process: escaping the data, creating the query, and executing the query.

It's vital not to omit the three lines at the top. When you insert user-entered information into a database, you must prevent the possibility that the user can create an invalid query or even maliciously alter the query. The mysqli_real_escape_string( ) function ensures strings are treated as data by escaping single quotes, and the intval( ) function converts the data to an integer, stripping away non- numbers in the process. This prevents MySQL from interpreting them as part of the query.

The mysqli_real_escape_string( ) function differs from mysql_escape_string( ) because mysqli_real_escape_string( ) respects the character encoding of your data. The older mysql_escape_string( ) is obsolete and is not available under mysqli .

After escaping the data, you take the fields and create an SQL statement. Although you can build the SQL directly inside mysqli_query( ) , assigning it to a variable such as $sql makes debugging easier because you can easily do a print $sql (or another form of logging) to discover if what you're actually passing MySQL is what you think you're passing.

When the SQL's complete, it's passed to MySQL using mysqli_query( ) . MySQL then receives the data, parses it, reports errors if they exist, executes the query, and returns a result.

This is standard stuff to most PHP programmers. You can continue to use this method in PHP 5 under mysqli , but as you'll see, there's a better method.

3.5.2 PHP 5: Binding Input Parameters

MySQL 4.1 introduces a new way to query a database. With this approach, you create a statement with input parameter placeholders. This template is then filled in with data when you execute the query.

Example 3-2 is a revised version of Example 3-1.

Example 3-2. Querying with bound input parameters

 // User-entered data $username = 'rasmus'; $password = 'z.8cMpdFbNAPw'; $zipcode  = 94088; // Prepare statement $sql = 'INSERT INTO users VALUES(?, ?, ?)'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Associate placeholders with data type and variable name     // 'ssi' tells MySQL you're passing two strings and an integer     mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $zipcode);          // Executing statement     mysqli_stmt_execute($stmt); } 

This is a big twist. There are question marks ( ? ) in the SQL where your variables used to be, and the calls to mysql_real_escape_string( ) and intval( ) have disappeared.

The new method separates querying a database into three parts. Before, you passed MySQL the SQL with the data and told MySQL to execute it in one step. Here, you first tell MySQL the query without any data. This is known as preparing the query . Next , you tell MySQL what types of information are going in each spot in the query and which PHP variables hold the data. This is called binding parameters . Finally, you pass along the data and execute the query. This is called executing the query .

When you prepare the query, you create an SQL statement that looks identical to what you normally write, but you substitute a question mark for each variable. Here's a before and after example:

 // Before $sql = "INSERT INTO events VALUES ('$name', '$password', $zipcode)"; // After $sql = 'INSERT INTO events VALUES(?, ?, ?)'; 

The new SQL statement is like a template that's going to be filled in later on, and the question marks tell MySQL where to place the data. Notice how there's no need to place single quotation marks around the first and second parameters. When you tell MySQL a parameter is a string, MySQL automatically quotes the variable for you. Parameter type identification is explained shortly.

The next step is to create a new MySQL statement. First, initialize a blank statement handle with mysqli_stmt_init( ) . Pass this and your SQL to mysqli_stmt_prepare( ) . This sends your statement to MySQL, where the server can check whether your syntax is correct. This function returns true if everything is okay and false if there's a problem.

 if (mysqli_stmt_prepare($stmt, $sql)) { 

All future interaction with the query goes through the statement handle, $stmt , instead of the database handle, $db .

Now that the statement is prepared, bind the parameters using mysqli_stmt_bind_param( ) :

 // Associate placeholders with data type and variable name mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $zipcode); 

This function takes a variable number of arguments. The first parameter is the statement handle. The second is a string that describes each SQL placeholder. If you want MySQL to treat the variable as text, use s ; for integers, i ; for all other numbers, d ; and for blobs, b . In this example, the first two placeholders are strings and the last one is an integer, so the argument is ssi .

The remaining arguments are the variables that you want PHP to pass to MySQL when the query is executed. This is different from passing the variables themselves , as in a traditional function call. MySQL does not take what's currently stored in the variables; instead, it takes what will be stored in the variables at the time of the query's execution. This is a big difference.

In this case, the end result would be the same either way because you don't alter the values of $username and the other two variables in between binding them and executing the query, but later on you see an example where the distinction is crucial.

You must have an equal number of question marks in your statement, characters in the placeholder types parameter, and arguments following that parameter. (Here there are three of each.) Failing to do so causes MySQL to return an error.

Finally, you instruct MySQL to execute the statement:

 // Executing statement mysqli_stmt_execute($stmt); 

Since you've already prepared the SQL and bound the variables, MySQL needs no new information, so mysqli_stmt_execute( ) is the least complicated part of the process.

You can also use prepared statements with mysqli 's OO interface. This example does the same thing as Example 3-2:

 // User-entered data $username = 'rasmus'; $password = 'z.8cMpdFbNAPw'; $zipcode  = 94088; // Prepare statement $sql = 'INSERT INTO users VALUES(?, ?, ?)'; $stmt = $db->stmt_init( ); if ($stmt->prepare($sql)) {     // Associate placeholders with data type and variable name     // 'ssi' tells MySQL you're passing two strings and an integer     $stmt->bind_param('ssi', $username, $password, $zipcode);          // Executing statement     $stmt->execute( ); } 

With the OO API, substitute mysqli_stmt_init($db) with $db->stmt_init( ) . This method returns a statement object, upon which you can invoke prepare( ) , bind_param( ) , and execute( ) methods .

Bound input and output parameters are associated with the specific variable, not the variable name. This matters when you call mysqli_stmt_bind_param( ) in a different scope than mysqli_stmt_execute( ) . For instance, this works:

 function execute_my_statement($stmt) {     // Execute prepared statement     mysqli_stmt_execute($stmt); } // Varibles defined in main scope $username = 'rasmus'; $password = 'z.8cMpdFbNAPw'; $zipcode  = 94088; // Prepare statement $sql = 'INSERT INTO users VALUES(?, ?, ?)'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Associate placeholders with data type and variable name     // 'ssi' tells MySQL you're passing two strings and an integer     mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $zipcode);     execute_my_statement($stmt); } 

Even though you're calling mysqli_stmt_execute( ) inside execute_my_statement( ) , mysqli contains a reference to $username from the main scope. This allows the query to execute properly.

However, this also means the following won't work:

 function execute_my_statement($stmt) {     // Varibles defined in local scope     $username = 'rasmus';     $password = 'z.8cMpdFbNAPw';     $zipcode  = 94088;     // Execute prepared statement     mysqli_stmt_execute($stmt); } // Prepare statement $sql = 'INSERT INTO users VALUES(?, ?, ?)'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Associate placeholders with data type and variable name     // 'ssi' tells MySQL you're passing two strings and an integer     mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $zipcode);     execute_my_statement($stmt); } 

In this example, you're binding $username in the main scope, but setting a different $username inside execute_my_statement( ) . Just because both variables have the same name ” $username ”doesn't mean they're references to each other.

3.5.3 PHP 5: Binding Input Parameters with Multiple Queries

The previous example inserted only one piece of data into the database. Where prepared statements really shine , however, is when you need to execute the same query multiple times. In these cases, there's no need to reprepare or rebind the query. You can just load new information into the bound variables and execute the statement another time. Example 3-3 shows how to enter multiple users into the database.

Example 3-3. Querying with bound input parameters multiple times

 $users = array(array('rasmus', 'z.8cMpdFbNAPw', 94088),                 array('zeev'  , 'asd34.23NNDeq',     0)); // Prepare statement $sql = 'INSERT INTO users VALUES(?, ?, ?)'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Associate placeholders with data type and variable name     // 'ssi' tells MySQL you're passing two strings and an integer     mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $zipcode);          foreach ($users as $user) {         // Place data into bound variables         list($username, $password, $zipcode) = $user;              // Execute prepared statement         mysqli_stmt_execute($stmt);     } } 

The initial setup in Example 3-3 is identical to previous examples, but instead of executing the statement once, mysqli_stmt_execute( ) lives inside a foreach loop. Every time the loop iterates, new values are placed in $username , $password , and $zipcode , and mysqli_stmt_execute( ) makes a new query. Even though mysqli_stmt_execute($stmt) hasn't changed, the query is different because the underlying data in the bound parameters has been updated.

This is where you can truly appreciate the benefit of using bound parameters. Not only is it significantly easier to re-execute a query using PHP, MySQL also executes them faster because it's already prepared the statement.

At present, you cannot bind array elements. It is tempting to omit the call to list and do the following:

 mysqli_stmt_bind_param($stmt, 'ssi', $user[0], $user[1], $user[2]);          foreach ($users as $user) {         // Execute prepared statement         mysqli_stmt_execute($stmt);     } 

This does not work. MySQLi will not grab elements from $user .

3.5.4 PHP 5: Binding Output Parameters

You can use bound parameters for more than just placing data into MySQL. You can also use them to retrieve data from MySQL.

Under the old MySQL extension, when you retrieved a row or column from the database, you needed to assign the result to a variable. This method still works with the mysqli extension, as shown earlier in the chapter.

However, mysqli also supports a new fetch method, mysqli_stmt_fetch( ) . When you use mysqli_stmt_fetch( ) , PHP variables are populated with MySQL table data, in a fashion similar to how mysqli_stmt_execute( ) pulls in values stored in PHP variables for a query, as shown in Example 3-4.

Example 3-4. Querying with bound output parameters

 // Prepare statement $sql = 'SELECT username FROM users'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Bind result variables     mysqli_stmt_bind_result($stmt, $username);          // Execute prepared statement     mysqli_stmt_execute($stmt);          // Place query data into bound result variables     while (mysqli_stmt_fetch($stmt)) {         // Print results         print "$username\n";     } }  rasmus   zeev  

The query is prepared like the earlier examples, but before executing the prepared statement, you bind the output using mysqli_stmt_bind_result( ) . The first parameter is the statement handle; the other parameters are the variables.

In this case, since the query returns only one column, $username is the only other argument. If the query returned multiple columns , then those variables would follow. For example, this query returns the username and the password :

 // Prepare statement $sql = 'SELECT username, password FROM users'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {     // Bind result variables     mysqli_stmt_bind_result($stmt, $username, $password); } 

Since the query can return multiple rows, mysqli_stmt_fetch( ) is placed inside a while loop. Each time the function is called, another row is placed in the output parameters. When the data is exhausted, the function returns NULL .

Of course, there's an OO version of Example 3-4:

 $sql = 'SELECT username FROM users'; $stmt = $db->stmt_init( ); if ($stmt->prepare($sql)) {     // Bind result variables     $stmt->bind_result($username);          // Execute prepared statement     $stmt->execute( );          // Place query data into bound result variables     while ($stmt->fetch( )) {         // Print results         print "$username\n";     } } 

This example is similar to the OO code for handling bound input parameters, but now you also use $stmt->bind_result( ) and $stmt->fetch( ) .

3.5.5 PHP 5: Bound Input and Output Parameters

You can bind both input and output parameters to the same query. For instance, the code in Example 3-5 gets all the usernames for people with a specific Zip Code.

Example 3-5. Querying with both bound input and bound output parameters

 $zipcode = 94088; // Prepare statement $sql = 'SELECT username FROM users WHERE zipcode = ?'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) {          // Associate placeholders with data type and variable name     mysqli_stmt_bind_param($stmt, 'i', $zipcode);          // Bind result variables     mysqli_stmt_bind_result($stmt, $username);          // Execute prepared statement     mysqli_stmt_execute($stmt);          // Place query data into bound result variables     while (mysqli_stmt_fetch($stmt)) {         // Print results         print "$username\n";     } }  rasmus  

This example combines code from Examples Example 3-3 and Example 3-4. After preparing the statement, you call both mysqli_stmt_bind_param( ) and mysqli_stmt_bind_result( ) . When you execute the query, MySQLi obeys both settings.

The inner while fetches each row using mysqli_stmt_fetch( ) and prints out the data. Here, there's only one record ” rasmus ”because the dataset is quite small.

You can bind the same variables for both input and output. However, it often makes sense to use separate variables, so you can more easily distinguish input data from output.

3.5.6 Object-Oriented Interface

You've already seen a few examples showing how to use the object-oriented interface to mysqli with bound parameters. This section fills in the remaining details.

To complement the mysqli object, there's also a MySQLi statement object, mysqli_stmt . This object is returned by mysqli_stmt_init( ) , and its methods are all the functions that begin with mysqli_stmt , such as mysqli_stmt_prepare( ) and mysqli_stmt_bind_param( ) , but without the mysqli_stmt prefix.

Example 3-6 rewrites Example 3-5 using the OO interface.

Example 3-6. Querying with bound parameters and an OO interface

 $zipcode = 94088; // Prepare statement $sql = 'SELECT username FROM users WHERE zipcode = ?'; $stmt = $db->stmt_init( ); if ($stmt->prepare($sql)) {          // Associate placeholders with data type and variable name     $stmt->bind_param('i', $zipcode);          // Bind result variables     $stmt->bind_result($username);          // Execute prepared statement     $stmt->execute( );          // Place query data into bound result variables     while ($stmt->fetch( )) {         // Print results         print "$username\n";     } } 

The call to mysqli_stmt_init($db) is now $db->stmt_init( ) . This method still has a leading stmt because it's a method of the mysqli object instead of a statement object.

The other calls now invoke methods on $stmt . The statement is prepared by calling $stmt->prepare($sql) and bound using $stmt->bind_param('i', $zipcode) and $stmt->bind_result($username) .

Since all the information about the statement is already stored in the object, you execute the query and fetch the data ( $stmt->execute( ) and $stmt->fetch( ) ) without passing any arguments.