Queries a Go-Go

Queries a Go-Go

One of the things you may be wondering about is the relative efficiency of constantly sending queries to the server within a script given how complex JOINs and other types of queries can get. It sure seems unfortunate that we constantly have to send the same queries and wait for our DBMS to parse, reparse, and recompile them.

It should come as no surprise that there is a solution to this problem. Prepared statements are queries that you send to the server for pre-compilation and readying and tell where you will store parameter values at a later time before you execute the query. They have a number of advantages over regular queries:

• The mysqli extension takes care of escaping the parameter values for you, saving you from having to call mysqli::real_escape_string to wrap the parameters in quotes.

• The performance of prepared statements when executed a number of times in a script is typically much faster than sending the data over the network each time. Whenever you want to execute one again, you only send the parameter data to the server, which takes up less space.

• You actually bind real PHP parameters to the parameters of the query. As long as the PHP parameters have their values set before you execute the query, the query's parameter set is properly filled out. These extremely convenient parameters are called bound parameters.

• You use prepared statements to bind columns in the result set to PHP variables so they are automatically placed in the variable each time you iterate over a new row. This is called bound results.

Bound Parameters

You use prepared statements by working with the mysqli_stmt class. To create one, you call the prepare method on the mysqli class that represents the connection to the appropriate server.

After you create the prepared statement, you call the bind_param method on the resulting mysqli_stmt class. This parameter takes a signature and the PHP parameters that contain the values to pass to the query. The only tricky part to this method is the format of the first parameterthe signature. It is specified as a string, where each character corresponds to the type of a single variable being bound. You should always make sure that there is one character in the string for each parameter required in the query. The possible values for the parameter types are shown in Table 12-1.

Table 12-1. Parameter Type Characters Mapped to MySQL Column Types

Parameter Type Char	Column Type in Database
I	All integer typed columns
D	Floating-point columns
B	BLOBs
S	Columns of all other types

The last requirement for prepared statements is that you use a question mark character (?) instead of parameters for your SQL query, as follows:

  $q_str = <<<EOQ
    DELETE * FROM Users WHERE user_name=?

EOQ;

  $stmt = @$conn->prepare($q_str);
  if ($stmt === FALSE)
  {
    echo "Connect Failed with: $conn->error<br/>\n";
    $conn->close();
    exit;
  }

  // otherwise, bind the parameters:
  $stmt->bind_param('s', $user_name);
  $user_name = $_POST['user_name'];

  // execute the prepared statement!
  $result = @$stmt->execute();
  if ($result === FALSE)
  {
    // handle error here ...
  }
  $stmt->close();
  $conn->close();

The example contains only one parameter, so there is only one character in the first parameter of the bind_param member function 's'. This implies that the parameter will be a string.

Finally, when we are done with bound statements, we call the close method on the mysqli_stmt class to clean up their resources.

Bound Results

Results returned from prepared statements are accessed by binding the results of a query to PHP variables in your script so that each time you iterate over rows, the variables hold the values of the fetched column data. To see the names (and types) of the columns retrieved, you use the result_metadata method on the mysqli_stmt class. (More information on this method can be found in the PHP Manual.)

To bind variables to the data returned by our query, our basic sequence of events will be

Thus, our complete listing using prepared statements looks as follows:

  $q_str = <<<EOQ
    SELECT user_name, full_name, birthdate
      FROM Users WHERE user_name LIKE ?

EOQ;

  $stmt = @$conn->prepare($q_str);
  if ($stmt === FALSE)
  {
    echo "Connect Failed with: $conn->error<br/>\n";
    $conn->close();
    exit;
  }

  // bind the parameters, adding a % after the username...
  $stmt->bind_param('s', $user_name);
  // note that prepared statements are careful to escape 
  // parameter values for us, so it is safe to use input
  // directly.
  $user_name = $_POST['user_name'] . '%';

  // bind the results (there are three columns)
  $stmt->bind_result($uname_col, $fname_col, $bday_col);
  // execute the prepared statement!
  $result = @$stmt->execute();
  if ($result === FALSE)
  {
    // handle error here ...
  }
  else
  {
    // fetch the row data one row at a time.
    while ($stmt->fetch() !== NULL)
    {
      echo <<< EOM
  User Name: $uname_col, Full Name: $fname_col<br/>
  Born On: $bday_col <br/><br/>

EOM;
    }

  // don't forget 
  $stmt->close();
  $conn->close();