CHAPTER 30

Using PHP with MySQL

PHP has supported MySQL since version 2. In fact, using MySQL with PHP eventually became so commonplace that for several years it was actually enabled by default. But perhaps the most indicative evidence of the strong bonds between the two technology camps was the release of an updated MySQL extension with PHP 5, known as MySQL Improved (and typically referred to as mysqli).

So why the need for a new extension? The reason is twofold. First, MySQL's rapid evolution prevented users who were relying on the original extension from taking advantage of new features such as prepared statements, advanced connection options, and security enhancements. Second, while the original extension certainly served programmers well, many considered the procedural interface outdated, preferring to create a native object-oriented interface that would not only more tightly integrate with other applications, but also offer the ability to extend that interface as desired. To resolve these deficiencies, the MySQL developers decided it was time to revamp the extension, not only changing its internal behavior to improve performance, but also incorporating additional capabilities to facilitate the use of features available only with these newer MySQL versions. A detailed list of the key enhancements follows:

• Object oriented: The mysqli extension is encapsulated into a class, encouraging use of what many consider to be a more convenient and efficient programming paradigm than PHP's traditional procedural approach. However, those preferring to embrace a procedural programming paradigm aren't out of luck, as a traditional procedural interface is also provided, although it won't be covered in this chapter.
• Prepared statements: Prepared statements eliminate overhead and inconvenience when working with queries intended for repeated execution, as is so often the case when building database-driven Web sites. Prepared statements also offer another important security-related feature in that they prevent SQL injection attacks.
• Transactional support: Although MySQL's transactional capabilities are available in PHP's original MySQL extension, the mysqli extension offers an object-oriented interface to these capabilities. While the relevant mysqli methods are introduced in this chapter, see Chapter 37 for a complete discussion of this topic.
• Enhanced debugging capabilities: The mysqli extension offers numerous methods for debugging queries, resulting in a more efficient development process.
• Embedded server support: As of MySQL 4.0, an embedded MySQL server library is available for users who are interested in running a complete MySQL server within a client application such as a kiosk or desktop program. The mysqli extension offers methods for connecting and manipulating these embedded MySQL databases.
• Master/slave support: As of MySQL 3.23.15, MySQL offers support for replication, although in later versions this feature has been improved substantially. Using the mysqli extension, you can ensure queries are directed to the master server in a replication configuration.

Thankfully, those familiar with the original MySQL extension will find the enhanced mysqli extension quite familiar because of the almost identical naming conventions. For instance, the database connection function is titled mysqli_connect() rather than mysql_connect(). Furthermore, all parameters and behavior for similar functions are otherwise externally identical to its predecessor. Therefore, if you're unlucky enough to still be using a version of PHP older than 5.0 (time to upgrade!), you'll still be able to take full advantage of this chapter, keeping in mind you need to slightly modify the names of the procedural function variants introduced here.

Handling Installation Prerequisites

As of PHP 5.0, MySQL support is no longer bundled with the standard PHP distribution. Therefore, you need to explicitly configure PHP to take advantage of this extension. In this section, you learn how to do so for both the Unix and Windows platforms.

Enabling the mysqli Extension on Linux/Unix

Enabling the mysqli extension on the Linux/Unix platform is accomplished by configuring PHP using the −-with-mysqli flag. This flag should point to the location of the mysql_config program available to MySQL 4.1 and greater.

Enabling the mysqli Extension on Windows

To enable the mysqli extension on Windows, you need to uncomment the following line from the php.ini file, or add it if it's not there:

extension=php_mysqli.dll

As is the case before enabling any extension, make sure the extension_dir directive points to the appropriate directory. See Chapter 2 for more information regarding configuring PHP.

Managing User Privileges

The constraints under which PHP interacts with MySQL are no different from those required of any other interface. A PHP script intent on communicating with MySQL must still connect to the MySQL server and must select a database to interact with. All such actions, in addition to the queries that would follow such a sequence, can be carried out only by a user possessing adequate privileges.

These privileges are communicated and verified when a user connects to the MySQL server, as well as every time a command requiring privilege verification is submitted. However, you need to identify the executing user only at the time of connection; unless another connection is made later within the script, that user's identity is assumed for the remainder of the script's execution. In the coming sections, you'll learn how to connect to the MySQL server and pass along these credentials.

Working with Sample Data

Learning a new topic tends to come easier when the concepts are accompanied by a set of cohesive examples. Therefore, the following table, products, located within a database named corporate, is used for all relevant examples in the following pages:

CREATE TABLE products (
   id INT NOT NULL AUTO_INCREMENT,
   sku VARCHAR(8) NOT NULL,
   name VARCHAR(25) NOT NULL,
   price DECIMAL(5,2) NOT NULL,
   PRIMARY KEY(id)

)

The table is populated with the following four rows:

---

+-------+----------+-----------------------+-------+
| id    | sku      | name                  | price |
+-------+----------+-----------------------+-------+
|     1 | TY232278 | AquaSmooth Toothpaste |  2.25 |
|     2 | PO988932 | HeadsFree Shampoo     |  3.99 |
|     3 | ZP457321 | Painless Aftershave   |  4.50 |
|     4 | KL334899 | WhiskerWrecker Razors |  4.17 |
+-------+----------+-----------------------+-------+

---

Using the mysqli Extension

PHP's mysqli extension offers all of the functionality provided by its predecessor, in addition to new features that have been added as a result of MySQL's evolution into a full-featured database server. This section introduces the entire range of features, showing you how to use the mysqli extension to connect to the database server, query for and retrieve data, and perform a variety of other important tasks.

Setting Up and Tearing Down the Connection

Interaction with the MySQL database is bookended by connection setup and teardown, consisting of connecting to the server and selecting a database, and closing the connection, respectively. As is the case with almost every feature available to mysqli, you can do this by using either an object-oriented approach or a procedural approach, although throughout this chapter only the object-oriented approach is covered.

If you choose to interact with the MySQL server using the object-oriented interface, you need to first instantiate the mysqli class via its constructor:

mysqli([string host [, string username [, string pswd

                    [, string dbname [, int port, [string socket]]]]]])

Those of you who have used PHP and MySQL in years past will notice this constructor accepts many of the same parameters as does the traditional mysql_connect() function.

Instantiating the class is accomplished through standard object-oriented practice:

$mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");

When instantiated with the parameters, you can start interacting with the database. If at one point you need to connect to another database server, or select another database, you can use the connect() and select_db() methods. The connect() method accepts the same parameters as the constructor, so let's just jump right to an example:

// Instantiate the mysqli class
$mysqli = new mysqli();

// Connect to the database server and select a database
$mysqli->connect("127.0.0.1", "catalog_user", "secret", "catalog_prod");

You can choose a database using the $mysqli->select_db method. The following example connects to a MySQL database server and then selects the catalog_prod database:

// Connect to the database server
$mysqli = new mysqli("localhost", "catalog_user", "secret");

// Select the database
$mysqli->select_db("catalog_prod");

Once a database has been successfully selected, you can then execute database queries against it. Executing queries, such as selecting, inserting, updating, and deleting information with the mysqli extension, is covered in later sections.

Once a script finishes execution, any open database connections are automatically closed and the resources are recuperated. However, it's possible that a page requires several database connections throughout the course of execution, each of which should be closed as appropriate. Even in the case where a single connection is used, it's nonetheless good practice to close it at the conclusion of the script. In any case, close() is responsible for closing the connection. An example follows:

$mysqli = new mysqli();
$mysqli->connect("127.0.0.1", "catalog_user", "secret", "catalog_prod");

// Interact with the database...

// close the connection
$mysqli->close()

Handling Connection Errors

Of course, if you're unable to connect to the MySQL database, then little else on the page is likely to happen as planned. Therefore, you should be careful to monitor connection errors and react accordingly. The mysqli extension includes a few features that can be used to capture error messages, or alternatively you can use exceptions (as introduced in Chapter 8). For example, you can use the mysqli_connect_errno() and mysqli_connect_error() methods to diagnose and display information about a MySQL connection error.

Retrieving Error Information

Developers always strive toward that nirvana known as bug-free code. In all but the most trivial of projects, however, such yearnings are almost always left unsatisfied. Therefore, properly detecting errors and returning useful information to the user is a vital component of efficient software development. This section introduces two functions that are useful for deciphering and communicating MySQL errors.

Retrieving Error Codes

Error numbers are often used in lieu of a natural-language message to ease software internationalization efforts and allow for customization of error messages. The errno() method returns the error code generated from the execution of the last MySQL function or 0 if no error occurred. Its prototype follows:

class mysqli {
    int errno
}

An example follows:

<?php
    $mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");
    printf("Mysql error number generated: %d", $mysqli->errno);
?>

This returns:

---

Mysql error number generated: 1045

---

Retrieving Error Messages

The error() method returns the most recently generated error message, or returns an empty string if no error occurred. Its prototype follows:

class mysqli {
    string error
}

The message language is dependent upon the MySQL database server, because the target language is passed in as a flag at server startup. A sampling of the English-language messages follows:

Sort aborted
Too many connections
Couldn't uncompress communication packet

An example follows:

<?php

    // Connect to the database server
    $mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");

    if ($mysqli->errno) {
        printf("Unable to connect to the database:<br /> %s",
                 $mysqli->error);
        exit();
    }

?>

For example, if the incorrect password is provided, you'll see the following message:

---

Unable to connect to the database:
Access denied for user 'catalog_user'@'localhost' (using password: YES)

---

Of course, MySQL's canned error messages can be a bit ugly to display to the end user, so you might consider sending the error message to your e-mail address, and instead displaying a somewhat more user-friendly message in such instances.

---

Tip MySQL's error messages are available in 20 languages and are stored in MYSQL-INSTALL-DIR/share/mysql/LANGUAGE/.

---

Storing Connection Information in a Separate File

In the spirit of secure programming practice, it's often a good idea to change passwords on a regular basis. Yet, because a connection to a MySQL server must be made within every script requiring access to a given database, it's possible that connection calls may be strewn throughout a large number of files, making such changes difficult. The easy solution to such a dilemma should not come as a surprise--store this information in a separate file and then include that file in your script as necessary. For example, the mysqli constructor might be stored in a header file named mysql.connect.php, like so:

<?php
    // Connect to the database server
    $mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");

?>

This file can then be included as necessary, like so:

<?php
    include "mysql.connect.php";
    // begin database selection and queries.

?>

Securing Your Connection Information

If you're new to using a database in conjunction with PHP, it might be rather disconcerting to learn that information as important as MySQL connection parameters, including the password, is stored in plain text within a file. Although this is the case, there are a few steps you can take to ensure that unwanted guests are not able to obtain this important data:

• Use system-based user permissions to ensure that only the user owning the Web server daemon process is capable of reading the file. On Unix-based systems, this means changing the file ownership to that of the user running the Web process and setting the connection file permissions to −r--------.
• If you're connecting to a remote MySQL server, keep in mind that this information will be passed in plain text unless appropriate steps are taken to encrypt that data during transit. Your best bet is to use Secure Sockets Layer (SSL) encryption.
• Several script-encoding products are available that will render your code unreadable to all but those possessing the necessary decoding privileges, while at the same time leaving the code's ability to execute unaffected. The Zend Guard (http://www.zend.com/) and ionCube PHP Encoder (http://www.ioncube.com/) are probably the best-known solutions, although several other products exist. Keep in mind that unless you have other specific reasons for encoding your source, you should consider other protection alternatives, such as operating system directory security, because they'll be quite effective for most situations.

Interacting with the Database

The vast majority of your queries will revolve around creation, retrieval, update, and deletion tasks, collectively known as CRUD. This section shows you how to formulate and send these queries to the database for execution.

Sending a Query to the Database

The method query() is responsible for sending the query to the database. Its prototype looks like this:

class mysqli {
    mixed query(string query [, int resultmode])
}

The optional resultmode parameter is used to modify the behavior of this method, accepting two values:

• MYSQLI_STORE_RESULT: Returns the result as a buffered set, meaning the entire set will be made available for navigation at once. This is the default setting. While this option comes at a cost of increased memory demands, it does allow you to work with the entire result set at once, which is useful when you're trying to analyze or manage the set. For instance, you might want to determine how many rows are returned from a particular query, or want to immediately jump to a particular row in the set.
• MYSQLI_USE_RESULT: Returns the result as an unbuffered set, meaning the set will be retrieved on an as-needed basis from the server. Unbuffered result sets increase performance for large result sets, but disallow the opportunity to do various things with the result set, such as immediately determine how many rows have been found by the query, or travel to a particular row offset. You should consider using this option when you're trying to retrieve a very large number of rows, because it will require less memory and produce a faster response time.

The following section shows how to use this method to send various queries to the database.

Retrieving Data

Chances are your application will spend the majority of its efforts retrieving and formatting requested data. To do so, you'll send the SELECT query to the database, and then iterate over the results, outputting each row to the browser, formatted in any manner you please.

The following example retrieves the sku, name, and price columns from the products table, ordering the results by name. Each row of results is then placed into three appropriately named variables, and output to the browser.

<?php

    $mysqli = new mysqli("127.0.0.1", "catalog_user", "secret", "catalog_prod");

    // Create the query
    $query = "SELECT sku, name, price FROM products ORDER by name";

// Send the query to MySQL
    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

    // Iterate through the result set
    while(list($sku, $name, $price) = $result->fetch_row())
        printf("(%s) %s: $%s <br />", $sku, $name, $price);

?>

Executing this example produces the following browser output:

---

(TY232278) AquaSmooth Toothpaste: $2.25
(PO988932) HeadsFree Shampoo: $3.99
(ZP457321) Painless Aftershave: $4.50
(KL334899) WhiskerWrecker Razors: $4.17

---

Keep in mind that executing this example using an unbuffered set would on the surface operate identically (except that resultmode would be set to MYSQLI_USE_RESULT instead), but the underlying behavior would indeed be different.

Inserting, Updating, and Deleting Data

One of the most powerful characteristics of the Web is its read-write format; not only can you easily post information for display, but you can also invite visitors to add, modify, and even delete data. In Chapter 13 you learned how to use HTML forms and PHP to this end, but how do the desired actions reach the database? Typically, this is done using a SQL INSERT, UPDATE, or DELETE query, and it's accomplished in exactly the same way as are SELECT queries. For example, to delete the AquaSmooth Toothpaste entry from the products table, execute the following script:

<?php

    $mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");

    // Create the query
    $query = "DELETE FROM products WHERE sku = 'TY232278'";

// Send the query to MySQL
    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

    // Tell the user how many rows have been affected
    printf("%d rows have been deleted.", $mysqli->affected_rows);

?>

Of course, provided the connecting user's credentials are sufficient (see Chapter 29 for more information about MySQL's privilege system), you're free to execute any query you please, including creating and modifying databases, tables, and indexes, and even performing MySQL administration tasks such as creating and assigning privileges to users.

Recuperating Query Memory

On the occasion you retrieve a particularly large result set, it's worth recuperating the memory required by that set once you've finished working with it. The free() method handles this task for you. Its prototype looks like this:

class mysqli_result {
    void free()

}

The free() method recuperates any memory consumed by a result set. Keep in mind that once this method is executed, the result set is no longer available. An example follows:

<?php

    $mysqli = new mysqli("localhost", "catalog_user", "secret", "catalog_prod");

    $query = "SELECT sku,  name,  price FROM products ORDER by name";
    $mysqli->query($query);

    $result = $mysqli->query($query, MYSQLI_STORE_RESULT);

    // Iterate through the result set
    while(list($sku, $name, $price) = $result->fetch_row())
        printf("(%s) %s: $%s <br />", $sku, $name, $price);

// Recuperate the query resources
    $result->free();

    // Perhaps perform some other large query

?>

Parsing Query Results

Once the query has been executed and the result set readied, it's time to parse the retrieved rows. Several methods are at your disposal for retrieving the fields comprising each row; which one you choose is largely a matter of preference, because only the method for referencing the fields differs. Each method is introduced in this section and accompanied by examples.

Fetching Results into an Object

Because you're likely using mysqli's object-oriented syntax, it makes sense to also manage the result sets in an object-oriented fashion. You can do so with the fetch_object() method. Its syntax follows:

class mysqli_result {
   array fetch_object()

}

The fetch_object() method is typically called in a loop, with each call resulting in the next row found in the returned result set populating an object. This object is then accessed according to PHP's typical object-access syntax. An example follows:

$query = "SELECT sku, name, price FROM products ORDER BY name";
$result = $mysqli->query($query);

while ($row = $result->fetch_object())
{
    $name = $row->name;
    $sku = $row->sku;
    $price = $row->price;
    printf("(%s) %s: %s <br />", $sku, $name, $price)";

}

Retrieving Results Using Indexed and Associative Arrays

The mysqli extension also offers the ability to manage result sets using both associative and indexed arrays using the fetch_array() and fetch_row() methods, respectively. Their prototypes follow:

class mysqli_result {
    mixed fetch_array ([int resulttype])
}

class mysqli_result {
   mixed fetch_row()

}

The fetch_array() method is actually capable of retrieving each row of the result set as an associative array, a numerically indexed array, or both, so this section demonstrates the fetch_array() method only rather than both methods, because the concepts are identical. By default, fetch_array() retrieves both arrays; you can modify this default behavior by passing one of the following values in as the resulttype:

• MYSQLI_ASSOC: Returns the row as an associative array, with the key represented by the field name and the value by the field contents.
• MYSQLI_NUM: Returns the row as a numerically indexed array, with the ordering determined by the ordering of the field names as specified within the query. If an asterisk is used (signaling the query to retrieve all fields), the ordering will correspond to the field ordering in the table definition. Designating this option results in fetch_array() operating in the same fashion as fetch_row().
• MYSQLI_BOTH: Returns the row as both an associative and a numerically indexed array. Therefore, each field could be referred to in terms of its index offset and its field name. This is the default.

For example, suppose you only want to retrieve a result set using associative indices:

$query = "SELECT sku, name FROM products ORDER BY name";
$result = $mysqli->query($query);
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $name = $row['name'];
    $sku = $row['sku'];
    echo "Product:  $name ($sku) <br />";

}

If you wanted to retrieve a result set solely by numerical indices, you would make the following modifications to the example:

$query = "SELECT sku, name, price FROM products ORDER BY name";
$result = $mysqli->query($query);
while ($row = $result->fetch_array(MYSQLI_NUM))
{
   $sku = $row[0];
   $name = $row[1];
   $price = $row[2];
   echo "($sku) $name: $price <br />";

}

Assuming the same data is involved, the output of both of the preceding examples is identical to that provided for the example in the query() introduction.

Determining the Rows Selected and Rows Affected

You'll often want to be able to determine the number of rows returned by a SELECT query, or the number of rows affected by an INSERT, UPDATE, or DELETE query. Two methods, introduced in this section, are available for doing just this.

Determining the Number of Rows Returned

The num_rows() method is useful when you want to learn how many rows have been returned from a SELECT query statement. Its prototype follows:

class mysqli_result {
    int num_rows

}

For example:

$query = "SELECT name FROM products WHERE price > 15.99";
$result = $mysqli->query($query);
printf("There are %f product(s) priced above $15.99.", $result->num_rows);

Sample output follows:

---

There are 5 product(s) priced above $15.99.

---

Keep in mind that num_rows() is only useful for determining the number of rows retrieved by a SELECT query. If you'd like to retrieve the number of rows affected by an INSERT, UPDATE, or DELETE query, use affected_rows(), introduced next.

Determining the Number of Affected Rows

This method retrieves the total number of rows affected by an INSERT, UPDATE, or DELETE query. Its prototype follows:

class mysqli_result {
    int affected_rows

}

An example follows:

$query = "UPDATE product SET price = '39.99' WHERE price = '34.99'";
$result = $mysqli->query($query);
printf("There were %d product(s) affected.", $result->affected_rows);

Sample output follows:

---

There were 2 products affected.

---

Working with Prepared Statements

It's commonplace to repeatedly execute a query, with each iteration using different parameters. However, doing so using the conventional query() method and a looping mechanism comes at a cost of both overhead, because of the repeated parsing of the almost identical query for validity, and coding convenience, because of the need to repeatedly reconfigure the query using the new values for each iteration. To help resolve the issues incurred by repeatedly executed queries, MySQL 4.1 introduced prepared statements, which can accomplish the same tasks as those described above at a significantly lower cost of overhead, and with fewer lines of code.

Two variants of prepared statements are available:

• Bound parameters: The bound-parameter variant allows you to store a query on the MySQL server, with only the changing data being repeatedly sent to the server, and integrated into the query for execution. For instance, suppose you create a Web application that allows users to manage store products. To jumpstart the initial process, you might create a Web form that accepts up to 20 product names, IDs, prices, and descriptions. Because this information would be inserted using identical queries (except for the data, of course), it makes sense to use a bound-parameter prepared statement.
• Bound results: The bound-result variant allows you to use sometimes unwieldy indexed or associative arrays to pull values from result sets by binding PHP variables to corresponding retrieved fields, and then use those variables as necessary. For instance, you might bind the URL field from a SELECT statement retrieving product information to variables named $sku, $name, $price, and $description.

Working examples of both of the preceding scenarios are examined a bit later, after a few key methods have been introduced.

Preparing the Statement for Execution

Regardless of whether you're using the bound-parameter or bound-result prepared statement variant, you need to first prepare the statement for execution by using the prepare() method. Its prototype follows:

class mysqli_stmt {
    boolean prepare()

}

A partial example follows. As you learn more about the other relevant methods, more practical examples are offered that fully illustrate this method's use.

<?php
    // Create a new server connection
    $mysqli = new mysqli("127.0.0.1", "catalog_user", "secret", "catalog_prod");

    // Create the query and corresponding placeholders
    $query = "SELECT sku, name, price, description
              FROM products ORDER BY sku";

    // Create a statement object
    $stmt = $mysqli->stmt_init();

    // Prepare the statement for execution
    $stmt->prepare($query);

    .. Do something with the prepared statement

    // Recuperate the statement resources
    $stmt->close();

    // Close the connection
    $mysqli->close();

?>

Exactly what "Do something..." refers to in the preceding code will become apparent as you learn more about the other relevant methods, which are introduced next.

Executing a Prepared Statement

Once the statement has been prepared, it needs to be executed. Exactly when it's executed depends upon whether you want to work with bound parameters or bound results. In the case of bound parameters, you'd execute the statement after the parameters have been bound (with the bind_param() method, introduced later in this section). In the case of bound results, you would execute this method before binding the results with the bind_result() method, also introduced later in this section. In either case, executing the statement is accomplished using the execute() method. Its prototype follows:

class stmt {
    boolean execute()

}

See the later introductions to bind_param() and bind_result() for examples of execute() in action.

Recuperating Prepared Statement Resources

Once you've finished using a prepared statement, the resources it requires can be recuperated with the close method. Its prototype follows:

class stmt {
    boolean close()

}

See the earlier introduction to prepare() for an example of this method in action.

Binding Parameters

When using the bound-parameter prepared statement variant, you need to call the bind_param() method to bind variable names to corresponding fields. Its prototype follows:

class stmt {
    boolean bind_param(string types, mixed &var1 [, mixed &varN])

}

The types parameter represents the datatypes of each respective variable to follow (represented by &var1, ... &varN) and is required to ensure the most efficient encoding of this data when it's sent to the server. At present, four type codes are available:

• i: All INTEGER types
• d: The DOUBLE and FLOAT types
• b: The BLOB types
• s: All other types (including strings)

The process of binding parameters is best explained with an example. Returning to the aforementioned scenario involving a Web form that accepts 20 URLs, the code used to insert this information into the MySQL database might look like the code found in Listing 30-1.

$stmt->execute();

    }

    // Recuperate the statement resources
    $stmt->close();

    // Close the connection
    $mysqli->close();

?>

Everything found in this example should be quite straightforward, except perhaps the query itself. Notice that question marks are being used as placeholders for the data, namely the user's ID and the URLs. The bind_param() method is called next, binding the variables $userid and $url to the field placeholders represented by question marks, in the same order in which they're presented in the method. This query is prepared and sent to the server, at which point each row of data is readied and sent to the server for processing using the execute() method. Finally, once all of the statements have been processed, the close() method is called, which recuperates the resources.

---

Tip If the process in which the array of form values are being passed into the script isn't apparent, see Chapter 13 for an explanation.

---

Binding Variables

After a query has been prepared and executed, you can bind variables to the retrieved fields by using the bind_result() method. Its prototype follows:

class mysqli_stmt {
    boolean bind_result(mixed &var1 [, mixed &varN])
}

For instance, suppose you want to return a list of the first 30 products found in the products table. The code found in Listing 30-2 binds the variables $sku, $name, and $price to the fields retrieved in the query statement.

Executing Listing 30-2 produces output similar to the following:

---

A0022JKL, pants, $18.99, Pair of blue jeans
B0007MCQ, shoes, $43.99, black dress shoes
Z4421UIM, baseball cap, $12.99, College football baseball cap

---

Retrieving Rows from Prepared Statements

The fetch() method retrieves each row from the prepared statement result and assigns the fields to the bound results. Its prototype follows:

class mysqli {
    boolean fetch()
}

See Listing 30-2 for an example of fetch() in action.

Using Other Prepared Statement Methods

Several other methods are useful for working with prepared statements, and are summarized in Table 30-1. Refer to their namesakes earlier in this chapter for an explanation of behavior and parameters.

Table 30-1. Other Useful Prepared Statement Methods

Method	Description
affected_rows()	Returns the number of rows affected by the last statement specified by the stmt object. Note this is only relevant to insertion, modification, and deletion queries.
free()	Recuperates memory consumed by the statement specified by the stmt object.
num_rows()	Returns the number of rows retrieved by the statement specified by the stmt object.
errno(mysqli_stmt stmt)	Returns the error code from the most recently executed statement specified by the stmt object.
error(mysqli_stmt stmt)	Returns the error description from the most recently executed statement specified by the stmt object.

Executing Database Transactions

Three new methods enhance PHP's ability to execute MySQL transactions, each of which is introduced in this section. Because Chapter 37 is devoted to an introduction to implementing MySQL database transactions within your PHP-driven applications, no extensive introduction to the topic is offered in this section. Instead, the three relevant methods concerned with committing and rolling back a transaction are introduced for purposes of reference. Examples are provided in Chapter 37.

Enabling Autocommit Mode

The autocommit() method controls the behavior of MySQL's autocommit mode. Its prototype follows:

class mysqli {
    boolean autocommit(boolean mode)
}

Passing a value of TRUE via mode enables autocommit, while FALSE disables it, in either case returning TRUE on success and FALSE otherwise.

Committing a Transaction

The commit() method commits the present transaction to the database, returning TRUE on success and FALSE otherwise. Its prototype follows:

class mysqli {
    boolean commit()
}

Rolling Back a Transaction

The rollback() method rolls back the present transaction, returning TRUE on success and FALSE otherwise. Its prototype follows:

class mysqli {
    boolean rollback()
}

Summary

The mysqli extension offers not only an expanded array of features over its older sibling, but also greater stability and performance. In fact, this extension alone should be enough reason to upgrade to PHP 5 and MySQL 4.1.3 if you haven't yet done so.

In the next chapter you'll learn all about PDO, yet another powerful database interface that is increasingly becoming the ideal solution for many PHP developers.