PHP- Connecting Database | MySqli examples

Mini Proj



PHP has supported MySQL almost since the project’s inception, including an API with the version 2
release. In fact, using MySQL with PHP eventually became so commonplace that for several years the extension was 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 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 within a series of classes,
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. The relevant methods are introduced in this
chapter, and Chapter 37 provides 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: An embedded MySQL server library was introduced
with the 4.0 release 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.
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.
Installation Prerequisites
As of PHP 5, 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 doesn’t exist:
extension=php_mysqli.dll
As is the case before enabling any extension, make sure PHP’s extension_dir directive points to the
appropriate directory. See Chapter 2 for more information regarding configuring PHP.
Using the MySQL Native Driver
Historically, PHP required that a MySQL client library be installed on the server from which PHP was
communicating with MySQL, whether the MySQL server also happened to reside locally or elsewhere.
PHP 5.3 removes this inconvenience by introducing a new MySQL driver named the MySQL Native Driver (also known as mysqlnd) that offers many advantages over its predecessors. The MySQL Native Driver is not a new API, but rather is a new conduit that the existing APIs (mysql, mysqli, and PDO_MySQL) can use in order to communicate with a MySQL server. Written in C, tightly integrated into PHP’s architecture, and released under the PHP license, I recommend using mysqlnd over the alternatives unless you have good reason for not doing so.

To use mysqlnd in conjunction with one of the existing extensions, you’ll need to recompile PHP,
including an appropriate flag. For instance, to use the mysqli extension in conjunction with the mysqlnd
driver, pass the following flag:
--with-mysqli=mysqlnd
If you plan on using both the PDO_MySQL and mysqli extensions, there’s nothing stopping you
from specifying both when compiling PHP:
%>./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd [other options]
The mysqlnd driver does suffer from some limitations. Currently it does not offer compression or
SSL support. Be sure to check the MySQL documentation at
http://dev.mysql.com/downloads/connector/php-mysqlnd for the latest information.
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 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 script initiates a connection 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(100) 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', 'corporate');
Once the connection has been made, 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('localhost', 'catalog_user', 'secret', 'corporate');
You can choose a database using the $mysqli->select_db method. The following example connects
to a MySQL database server and then selects the corporate database:
// Connect to the database server
$mysqli = new mysqli('localhost', 'catalog_user', 'secret');

// Select the database
$mysqli->select_db('corporate');
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('localhost', 'catalog_user', 'secret', 'corporate');
// 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 going 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', 'corporate');
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 it 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', 'corporate');
if ($mysqli->errno) {
printf("Unable to connect to the database:<br /> %s",
$mysqli->error);
exit();
}
?>

To Insert into table

$query=$query="INSERT INTO  products (

sku ,
N  

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-INSTALLDIR/
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', 'corporate');
?>
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 400 (only the owner possesses read
access).
• 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
(www.zend.com) and ionCube PHP Encoder (www.ioncube.com) are probably the
best-known solutions, although several other products exist. Keep in mind that
unless you have 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 you might 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.
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('localhost', 'catalog_user', 'secret', 'corporate');
// 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', 'corporate');
// 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', 'corporate');
$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();
$mysqli->close();

?>
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.

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];
printf("(%s) %s: %d <br />", $sku, $name, $price);
}
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 tasks 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 boundparameter
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 using 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(string query)
}
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('localhost', 'catalog_user', 'secret', 'corporate');
// 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.
Listing 30-1. Binding Parameters with the mysqli Extension
<?php
// Create a new server connection
$mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate');
// Create the query and corresponding placeholders
$query = "INSERT INTO products SET id=NULL, sku=?,
name=?, price=?";
// Create a statement object
$stmt = $mysqli->stmt_init();
// Prepare the statement for execution
$stmt->prepare($query);
// Bind the parameters
$stmt->bind_param('ssd', $sku, $name, $price);
// Assign the posted sku array
$skuarray = $_POST['sku'];
// Assign the posted name array
$namearray = $_POST['name'];
// Assign the posted price array
$pricearray = $_POST['price'];
// Initialize the counter
$x = 0;
// Cycle through the array, and iteratively execute the query
while ($x < sizeof($skuarray)) {
$sku = $skuarray[$x];
$name = $namearray[$x];
$price = $pricearray[$x];
$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.
Listing 30-2. Binding Results with the mysqli Extension
<?php
// Create a new server connection
$mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate');
// Create query
$query = 'SELECT sku, name, price FROM products ORDER BY sku';
// Create a statement object
$stmt = $mysqli->stmt_init();
// Prepare the statement for execution
$stmt->prepare($query);
// Execute the statement
$stmt->execute();
// Bind the result parameters
$stmt->bind_result($sku, $name, $price);

// Cycle through the results and output the data
while($stmt->fetch())
printf("%s, %s, %s <br />", $sku, $name, $price);
// Recuperate the statement resources
$stmt->close();
// Close the connection
$mysqli->close();
?>
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; they 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.

Method Description
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. 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—when
used in conjunction with the new mysqlnd driver— unparalleled stability and performance.
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.

Reactions: