Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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>