H.3 Using the New Features

This section explains some of the new features and illustrates them with short examples. We show you:

• How to use the new basic features

• Basic examples of how to prepare and execute statements

• How to profile your MySQL function calls

We don't discuss features for working with replicated servers, the new transaction features, or how to set up compressed or encrypted connections

H.3.1 Basic Features

With the regular library, you connect to a server and select a database using a fragment such as the following:

$connection = mysql_connect("localhost", "fred", "shhh");

mysql_select_db("winestore", $connection);

With the improved library, you can combine these two steps as follows:

$connection = mysqli_connect("localhost", "fred", "shhh", "winestore");

As shown in Example H-2, you can still use the old approach if you want to.

Both the regular and improved libraries have two query functions. The regular library has mysql_query( ) and mysql_unbuffered_query( ), while the improved library has mysqli_query( ) and mysql_real_query( ). As we showed in the previous section, mysql_query( ) and mysqli_query( ) are equivalent.

The mysqli_real_query( ) function can be used for either buffered or unbuffered output; it can provide normal output or the same features as mysql_unbuffered_query( ). After you've called mysql_real_query( ), you need to call either mysql_use_result( ) or mysql_store_result( ) to specify how results are to be retrieved. If you call mysql_use_result( ), then rows are buffered on demand and the behavior is the same as mysql_unbuffered_query( ). If you call mysql_store_result( ), then all rows are buffered and the behavior is the same as mysql_query( ).

Consider an example that uses the regular library and mysql_query( ):

$result = mysql_query("SELECT * FROM customer", $connection);

This example runs the query and buffers all result rows. To do the same thing with the improved library, you can do either of the following:

// Simplified version

$result = mysqli_query($connection, "SELECT * FROM customer");



// Two-step version

if (mysqli_real_query($connection, "SELECT * FROM customer"))

  $result = mysqli_store_result($connection);

With the regular library, use mysql_unbuffered_query( ) as follows:

$result = mysql_unbuffered_query("SELECT * FROM customer", $connection);

To do the same thing with the improved library, do the following:

if (mysqli_real_query($connection, "SELECT * FROM customer"))

  $result = mysqli_use_result($connection);

If you prefer the object-oriented style of PEAR DB over the procedural style of the regular library, then you'll enjoy using the improved library. Here's an example fragment that uses the new object-oriented style:

<?php

$connection = mysqli_connect("localhost", "fred", "drum", "winestore");



$result = $connection->query("SELECT * FROM wine");



while ($row = $result->fetch_array( ))

{

  foreach($row as $element)

    print "$element ";

  print "\n";

}



$connection->close( );

?>

H.3.2 Preparing and Executing Queries

With the improved library, you can separate query preparation from query execution. This means that if you need to repeat the same query many times, but with different values, you can improve your application's performance.

To use this approach, write an SQL query that contains placeholders instead of values. Then issue a prepare statement that asks MySQL to do as much parsing and preparation of the statement as possible in advance. Then, repeatedly replace the placeholders with values and execute the prepared query.

H.3.2.1 Inserting data

Consider an example of inserting data with prepare and execute. Suppose you want to insert more than one row into the items table from the winestore database. The table was created with the following statement:

CREATE TABLE items (

  cust_id int(5) NOT NULL,

  order_id int(5) NOT NULL,

  item_id int(3) NOT NULL,

  wine_id int(4) NOT NULL,

  qty int(3),

  price decimal(5,2),

  PRIMARY KEY (cust_id,order_id,item_id)

) type=MyISAM;

Without prepare and execute, you'd insert two rows into the table using a fragment such as the following:

// first row to be inserted

$cust_id = 14;

$order_id = 3;

$item_id = 1;

$wine_id = 770;

$qty = 2;

$price = 16.95;



$result = mysqli_query($connection, 

  "INSERT INTO items VALUES ({$cust_id}, {$order_id}, {$item_id}, 

   {$wine_id}, {$qty}, {$price})");



if (mysqli_affected_rows($connection) != 1)

  die("Had a problem");



// Change the variables

$cust_id = 14;

$order_id = 3;

$item_id = 2;

$wine_id = 184;

$qty = 12;

$price = 12.90;



// Insert the second row

$result = mysqli_query($connection, 

  "INSERT INTO items VALUES ({$cust_id}, {$order_id}, {$item_id}, 

   {$wine_id}, {$qty}, {$price})");



if (mysqli_affected_rows($connection) != 1)

  die("Had a problem");

Using the prepare and execute approach, you insert the same rows using the following fragment:

// Create a query with placeholders

$query = "INSERT INTO items VALUES (?,?,?,?,?,?)";



// Prepare the query

$stmt = mysqli_prepare($connection, $query);



// first row to be inserted

$cust_id = 14;

$order_id = 3;

$item_id = 1;

$wine_id = 770;

$qty = 2;

$price = 16.95;



// Bind the variables to the placeholders

mysqli_bind_param($stmt,

  array(MYSQLI_BIND_INT, MYSQLI_BIND_INT, MYSQLI_BIND_INT,

        MYSQLI_BIND_INT, MYSQLI_BIND_INT, MYSQLI_BIND_DOUBLE),

  $cust_id, $order_id, $item_id, $wine_id, $qty, $price);



// Insert the first row

mysqli_execute($stmt);

if (mysqli_stmt_affected_rows($stmt) != 1)

  die("Had a problem");



// Change the variables

$cust_id = 14;

$order_id = 3;

$item_id = 2;

$wine_id = 184;

$qty = 12;

$price = 12.90;



// Insert the second row

mysqli_execute($stmt);

if (mysqli_stmt_affected_rows($stmt) != 1)

  die("Had a problem");



// Close the statement

mysqli_stmt_close($stmt);

This approach requires more steps, but it can tremendously improve performance when scaled up to large numbers of queries. The code just shown starts by creating and preparing a query with the following lines of code:

// Create a query with placeholders

$query = "INSERT INTO items VALUES (?,?,?,?,?,?)";



// Prepare the query

$stmt = mysqli_prepare($connection, $query);

The question mark characters ? represent where values will be placed when the query is executed. The mysqli_prepare( ) function returns a prepared query statement that's saved in the variable $stmt.

After you create variables and assign them the values to be inserted, you bind the variables to the placeholders in the prepared statement using the following fragment:

// Bind the variables to the placeholders

mysqli_bind_param($stmt,

  array(MYSQLI_BIND_INT, MYSQLI_BIND_INT, MYSQLI_BIND_INT,

        MYSQLI_BIND_INT, MYSQLI_BIND_INT, MYSQLI_BIND_DOUBLE),

  $cust_id, $order_id, $item_id, $wine_id, $qty, $price);

The first parameter is the prepared statement. The second parameter is an array that specifies the types of each of the variables that are bound to the statement. In this example, there are six variables, where the first five are integers and the last is a double precision number. You can also specify MYSQL_BIND_STRING to bind a string variable and MYSQL_SEND_DATA to bind a very large amount of data. The remaining parameters are the variables themselves, and they must match the elements in the second parameter, both in the number of elements and their order.

Once the variables are bound to the query, you can execute the statement. In the fragment, this is done with:

// Insert the first row

mysqli_execute($stmt);



if (mysqli_stmt_affected_rows($stmt) != 1)

  die("Had a problem");

The function mysqli_stmt_affected_rows( ) is the same as mysqli_affected_rows( ), but works for statements instead of regular queries.

Now that the query is prepared and the variables are bound, to insert another row, all you need to do is change the values of the variables and reexecute the statement:

// Change the variables

$cust_id = 14;

$order_id = 3;

$item_id = 2;

$wine_id = 184;

$qty = 12;

$price = 12.90;



// Insert the second row

mysqli_execute($stmt);



if (mysqli_stmt_affected_rows($stmt) != 1)

  die("Had a problem");

Once you've finished with a prepared statement, you can free the resources that are associated with it using:

// Close the statement

mysqli_stmt_close($stmt);

The code we've shown is somewhat artificial. Normally, you'd do the preparation and binding, and then run a loop that creates the input, assigns the values to the placeholders, and executes the query.

H.3.2.2 Retrieving data

You can also repeat a SELECT query using the prepare and execute approach. This is useful if you want to run the same query, but want to use different values each time in the WHERE clause. For example, this would be a useful tool in our online winestore search feature, where wine prices are repeatedly retrieved for different wines.

Let's start by showing the old way of doing things. Wine prices can be retrieved using the following function (which is based on the showPricing( ) function described in Chapter 16 but rewritten to use the improved MySQL library):

function showPricing($connection)

{

  global $wineID;



  // Find the price of the cheapest inventory

  $query = "SELECT min(cost) FROM inventory

             WHERE wine_id = {$wineID}";



   // Run the query

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



   // Retrieve and return the price

   $row = mysqli_fetch_array($result, MYSQLI_BOTH);



   return $row["min(cost)"];

}

To modify the function to use the prepare and execute approach, you need to have an extra variable that stores a prepared statement. If the statement isn't yet prepared, the function will prepare it. If it is prepared, then it'll use that statement. Here's the rewritten code:

function showPricing($connection)

{

  global $statement, $wineID;



   if (empty($statement))

   {

     // Find the price of the cheapest inventory

     $query = "SELECT min(cost) FROM inventory

               WHERE wine_id = ?";



      // Prepare the query

      $statement = mysqli_prepare($connection, $query);



      // Bind the $wineID to the placeholder

      mysqli_bind_param($statement, array(MYSQLI_BIND_INT), $wineID);

   }



   // Run the query

   mysqli_execute($statement);



   $cost = "";



   // Bind the output -- links min(cost) to $cost

   mysqli_bind_result($statement, $cost);



   // Retrieve and return the price

   mysqli_fetch($statement);



   return $cost;

}

The code is a little longer than the old way of doing things, but it's much faster when prices are retrieved many times. The following fragment checks if the statement has been prepared and, if not, it prepares it and binds the $wineID to the placeholder:

   if (empty($statement))

   {

     // Find the price of the cheapest inventory

     $query = "SELECT min(cost) FROM inventory

               WHERE wine_id = ?";



      // Prepare the query

      $statement = mysqli_prepare($connection, $query);



      // Bind the $wineID to the placeholder

      mysqli_bind_param($statement, array(MYSQLI_BIND_INT), $wineID);

   }

This process is explained in the previous section. However, in this example, we've declared the $wineID as global in the function; at the time of writing, passing the variable as a parameter by reference didn't work.

Once the statement is prepared, it is executed with:

   // Run the query

   mysqli_execute($statement);

Now that the query has been run, we need to retrieve the results. To do this, you need to bind the output to one or more variables. In our example, there's only one attribute retrieved by the query, min(cost). This is bound to the variable $cost using the following fragment:

   // Bind the output -- links min(cost) to $cost

   mysqli_bind_result($statement, $cost);

If there were instead two attributes returned by the query, you'd bind both using two variables:

   mysqli_bind_result($statement, $var1, $var2);

Once the variable has been bound to the output, you can retrieve the row of data and the value of $cost:

   // Retrieve and return the price

   mysqli_fetch($statement);



   return $cost;

Note that you must use mysqli_fetch( ) to retrieve rows from an executed query and that it takes the statement as its parameter.

H.3.3 Profiling Queries

The new query profiler allows you to collect information about how the improved MySQL library is functioning. Once configured, it reports information including:

Source file data

The name and line number of each improved MySQL function library call.

Timings

The total script execution time, and the execution time of each MySQL function call.

Parameters and warnings

Information such as the machine host name, database server user name, and warnings about missing statements (for example, warning you that you didn't call mysqli_close( )).

Query data

For each MySQL function call, the report lists information such as the function called, its return value, the query string passed as a parameter, the output of the EXPLAIN statement for that query (which shows how the query was evaluated), and the number of affected rows.

You can use this information to correct bugs and warnings and check the query speed (and take corrective action if it isn't acceptable). The information is output in an XML format.

To use the profiler, call the mysqli_profiler( ) function at the beginning of each script you want to profile. The easiest option is to write the report to a file. You can do this with:

mysqli_profiler(MYSQLI_PR_REPORT_FILE, "/tmp/report");

Replace /tmp/report with the directory and file you want to write the report to. You can also write to the stderr device on a Unix platform by using:

mysqli_profiler(MYSQLI_PR_REPORT_STDERR);

You can also write to a network socket, but we don't discuss this here.

In part, the output of a report has the following example format:

<query>

<functionname>mysqli_query</functionname>

<fileinfo>

<filename>/usr/local/apache2/htdocs/test.php</filename>

<line>7</line>

</fileinfo>

<timeinfo>

<execution_time>0.000636</execution_time>

</timeinfo>

<query_string>SELECT * FROM wine</query_string>

<explain>

<id>1</id>

<select_type>SIMPLE</select_type>

<table>wine</table>

<type>ALL</type>

<possible_keys>(null)</possible_keys>

<key>(null)</key>

<key_len>(null)</key_len>

<ref>(null)</ref>

<rows>1048</rows>

<Extra></Extra>

</explain>

<affected_rows>-1</affected_rows>