Zend PHP 5 Certification Study Guide (2014)

Database Programming

Most applications that you will work with or encounter will involve the use of some sort of data storage container. In some cases, you will need nothing more than files for this purpose, but often, that container is some sort of database engine. PHP provides access to a great number of different database systems, many of which are relational in nature and can be interrogated using Structured Query Language (SQL). In order to utilize these databases, it is important to have a firm grasp of SQL, as well as the means to connect to and interact with databases from PHP. This chapter reviews the basic concepts of SQL and database connectivity from PHP using PHP Data Objects (PDO).

An Introduction to Relational Databases and SQL

Most developers will use relational databases. A relational database is structured, as its name implies, around the relationships between the entities it contains.

The fundamental data container in a relational database is a database or schema. Generally speaking, a schema represents a namespace within which the characteristics of a common set of data are defined. These may include the structure of the data, the data itself, a set of credentials and permissions that determine who has access to the schema’s contents, and so on.

The term database is often used interchangeably when referring either to a specific schema or to a server on which the database is stored.

The data is stored in structured containers called tables. A table is a bidimensional collection of zero or more rows, each of which can contain one or more columns. The columns define the structure of the data, while the rows define the data itself.

Indices

Relational databases are traditionally biased toward read operations; this means that a certain amount of efficiency is sacrificed when data is written to a table so that future read operations can perform better. In other words, databases are designed so that data can be searched and extracted as efficiently as possible.

This is accomplished by means of indices, which make it possible to organize the data in a table according to one or more columns. Indices are one of the cardinal elements of relational databases and, if properly used, can have a significant impact on the ability of your applications to manipulate data efficiently. Misuse or lack of indices is one of the most common causes of performance problems in database-driven applications.

Indices can usually be created on one or more columns of a table. Generally speaking, they should be created on those columns that you are going to use later in search operations; fewer columns will often cause the engine to ignore the index (thus wasting it), and more columns will require extra work, thus reducing the effectiveness of the index.

An index can also be declared as unique, in which case it will prevent the same combination of values from appearing more than once in the table. For example, if you create a unique index on the columns ID and FirstName, the combination ID = 10 and FirstName = 'Davey' will be allowed only once, without preventing its individual components from appearing any number of times. For example, ID = 11 and FirstName == 'Ben' would be perfectly acceptable. Even ID = 12 and FirstName == 'Davey' would be acceptable in a compound index.

Primary keys are a special type of unique index that is used to determine the “natural” method of uniquely identifying a row in a table. From a practical perspective, primary keys differ from unique indices only in the fact that there can only be one primary key in each table, while an arbitrary number of unique indices can exist.

Relationships

As we mentioned at the beginning of this chapter, data relationships are one of the most important aspects of relational databases. Relationships are established between tables to ensure the consistency of data at all times; for example, if your database contains a table that holds the names of your customers and another table that contains their addresses, you don’t want to be able to delete rows from one if there are still corresponding rows in the other.

Relationships between tables can be of three types:

·        One-to-one: only one row in the child table can correspond to each row in the parent table

·        One-to-many: an arbitrary number of rows in the child table can correspond to any one row in the parent table

·        Many-to-many: an arbitrary number of rows in the child table can correspond to an arbitrary number of rows in the parent table

It’s interesting to note that the SQL language only offers facilities for directly creating one-to-one and one-to-many relationships. Many-to-many relationships require a bit of trickery and the use of an intermediate table to hold the relationships between the parent and child tables.

SQL Data Types

SQL is the most common database manipulation language (DML) used in relational databases, and SQL-92, as defined by the American National Standards Institute (ANSI), is its most commonly used variant. Although SQL is considered a “standard” language, it is somewhat limited in relation to the real-world needs of almost any application. As a result, practically every database system in existence implements its own “dialect” of SQL while, for the most part, maintaining full compatibility with SQL-92. This makes writing truly portable applications very challenging.

SQL supports a number of data types, which provide a greater degree of flexibility than PHP in how the data is stored and represented. For example, numeric values can be stored using a variety of types:

SQL Numeric Type

Description

int or integer

Signed integer number, 32 bits in length

smallint

Signed integer number, 16 bits in length

real

Signed floating-point number, 32 bits in length

float

Signed floating-point number, 64 bits in length

To these, most database systems add their own, nonstandard variants; for example, MySQL supports a data type called tinyint, which is represented as a one-byte signed integer number.

Clearly, all of these data types are converted into either integers or floating-point numbers when they are retrieved into a PHP variable, which is not normally a problem. However, you need to be aware of the precision and range of each data type when you write data from a PHP script into a database table, since it’s quite possible that you will cause an overflow (which a database system should at least report as a warning).

This is even more apparent—and, generally, more common—when you deal with string data types. SQL-92 defines two string types:

SQL String Type

Description

char

Fixed-length character string

varchar

Variable-length character string

The only difference between these two data types is that a char string will always have a fixed length, regardless of how many characters it contains (the string is usually padded with spaces to the column’s length). In both cases, however, a string column must be given a length (usually between 1 and 255 characters, although some database systems do not follow this rule), which means that any string coming from PHP, where it can have an arbitrary length, can be truncated, usually without even a warning, thus resulting in the loss of data.

Most database systems also define an arbitrary-length character data type (usually called text) that more closely resembles PHP’s strings. However, this data type usually comes with a number of constraints (such as a maximum allowed length and severe limitations on search and indexing capabilities). Therefore, you will still be forced to use char and (more likely) varchar, with all of their limitations.

Strings in SQL are enclosed by single quotation strings:

'This is a string, and here''s some escaping: ''Another String'''

There a few important items to note: first of all, standard SQL does not allow the insertion of any special escape sequences like \n. In addition, single quotation marks are normally escaped using another quotation mark; however, and this is very important, not all database systems follow this convention. Luckily, however, almost every database access extension that supports PHP also provide specialized functions that will take care of escaping the data for you.

SQL character strings act differently from PHP strings. In most cases, the former are “true” text strings, rather than collections of binary characters; therefore, you won’t be able to store binary data in an SQL string. Most database systems provide a separate data type (usually called “BLOB”, for Binary Large OBject) for this purpose.

The data type that perhaps causes the most frequent problems is datetime, which encapsulates a given time and date. In general, a database system’s ability to represent dates goes well beyond PHP’s, opening the door to all sorts of potential problems, which are best solved by keeping all of your date-manipulation operations inside the database itself. Only extract dates in string form when they are needed.

The last data type that we will examine is NULL. This is a special data type that has a distinct meaning that is not directly interchangeable with any other value of any other data type; NULL is not the same as 0, or an empty string. A column is set to NULL to indicate that it does not contain any value. When defining your tables, you can specify whether a column should allow NULL.

Columns that allow NULL values cannot be used as part of a primary key.

Creating Databases and Tables

The creation of a new database is relatively simple:

CREATE DATABASE <dbname>;

CREATE SCHEMA <dbname>;

These two forms are equivalent to each other—<dbname> is the name of the new database you want to create. As you can see, there is no mechanism for providing security or access control—this is, indeed, a shortcoming of SQL that is solved by each database system in its own way.

The creation of a table is somewhat more complex, given that you need to declare its structure as well:

CREATE TABLE <tablename> (

    <col1name> <col1type> [<col1attributes>],

    [...

    <colnname> <colntype> [<colnattributes>]]

);

As you can see, it is necessary to declare a column’s data type; as you probably have guessed, most database systems are very strict about data typing, unlike PHP. Here’s the declaration for a simple table that we’ll use throughout the remainder of this chapter:

CREATE TABLE book (

    id INT NOT NULL PRIMARY KEY,

    isbn VARCHAR(13),

    title VARCHAR(255),

    author VARCHAR(255),

    publisher VARCHAR(255)

);

Here, we start by declaring a column of type INT that cannot contain NULL values and is the primary key of our table. The other columns are all VARCHARs of varying length (note how we are using 255 as the maximum allowable length; this is a safe bet and it is true in many, but not all, database systems).

Creating Indices and Relationships

Indices can be created when the table is created, as we did with the primary key above; alternatively, you can create them separately:

CREATE INDEX <indexname>

ON <tablename> (<column1>[, ..., <columnn>]);

For example, suppose we wanted to create a unique index on the isbn column of the book table we created earlier:

CREATE INDEX book_isbn ON book (isbn);

The name of the index is, of course, entirely arbitrary and only significant when you are deleting it; however, it must still be unique and abide by the naming rules described above.

Foreign-key relationships are created either when a table is created or at a later date with an altering statement. For example, suppose we wanted to add a table that contains a list of all of the chapter titles for every book:

CREATE TABLE book_chapter

    isbn VARCHAR(13) REFERENCES book (id),

    chapter_number INT NOT NULL,

    chapter_title VARCHAR(255)

);

This code creates a one-to-many relationship between the parent table book and the child table book_chapter based on the isbn field. Once this table is created, you can only add a row to it if the ISBN you specify exists in book. In this way, foreign keys help you maintain the relationship between a chapter and a book. In this case a row in book_chapter can not be orphaned—that is, stored without a parent row in book.

To create a one-to-one relationship, simply make the connective columns of a one-to-many relationship the primary key of the child table.

Dropping Objects

The act of deleting an object from a schema—be it a table, an index, or even the schema itself—is called dropping. It is performed by a variant of the DROP statement:

DROP TABLE book_chapter;

A good database system that supports referential integrity will not allow you to drop a table if doing so would break the consistency of your data. Thus, the book table cannot be deleted until book_chapter is dropped.

The same technique can be used to drop an entire schema:

DROP SCHEMA my_book_database;

Adding and Manipulating Data

While most of the time you will be retrieving data from a database, being able to insert it is essential to using it later. This is done by means of the INSERT statement, which takes two forms:

INSERT INTO <tablename> VALUES (<field1Value>[, ..., <fieldNValue>]);

INSERT INTO <tablename>

(<field1>[, ..., <fieldN>])

VALUES

(<field1Value>[, ..., <fieldNValue>]);

The first form is used when you want to provide values for every column in your table; in this case, the column values must be specified in the same order in which the columns appear in the table declaration. This form is almost never ideal; for one thing, you may not even be able to specify a value for each column—some of the columns may be calculated automatically by the system, and forcing a value onto them may actually cause an error to be thrown. In addition, using this form implies that you expect the order of the columns to never change—this is never a good idea if you plan for your application to run for more than a month!

In its second form, the INSERT statement consists of three main parts. The first part tells the database engine which table to insert the data into; the second part indicates the columns for which we’re providing a value; and the third part contains the actual data to insert. Note how string values are enclosed in single quotes and single quotes within the string are escaped with a \. Here’s an example:

INSERT INTO book (isbn, title, author)

VALUES ('0812550706', 'Ender\'s Game', 'Orson Scott Card');

Adding records to the database is, of course, not very useful without the ability to modify them. To update records, you can use the UPDATE statement, which can alter the value of one or more columns for all rows, or for a specific subset of rows by means of a WHERE clause. For example, the following UPDATE statement updates the publisher for all records in the book table to a value of 'Tor Science Fiction':

UPDATE book SET publisher = 'Tor Science Fiction';

Since it is not likely that all books in the table will have the same publisher (and, if they did, you wouldn’t need a database column to tell you), you can restrict the range of records over which the UPDATE statement operates:

UPDATE book

SET publisher = 'Tor Science Fiction'

    , author = 'Orson S. Card'

WHERE isbn = '0812550706';

This UPDATE statement will update only the record (or records) for which isbn is equal to the value '0812550706'. Notice also that this statement illustrates another feature of the UPDATE statement: it is possible to update multiple columns at a time using the same statement.

Removing Data

In a dynamic application, data never remains constant. It always changes—and, sometimes, it becomes superfluous and needs to be deleted. SQL database engines implement the DELETE statement for this purpose:

DELETE FROM book;

This simple statement will remove all records from the book table, leaving behind an empty table. If you have foreign keys defined, this may even delete records in other tables that reference the parent record that is being deleted. At times, it is necessary to remove all records from tables, but most of the time, you will want to provide parameters limiting the deletion to specific records. Again, a WHERE clause achieves this:

DELETE FROM book WHERE isbn = '0812550706';

Retrieving Data

As we mentioned earlier, relational databases are biased toward read operations; therefore, it follows that the most common SQL statement is designed to extract data from a database.

To retrieve data from any SQL database engine, you use a SELECT statement; SELECT statements can be very simple or incredibly complex, depending on your needs. The most basic form, however, is simple:

SELECT * FROM book;

The statement begins with the verb or action keyword SELECT, followed by a comma-separated list of columns to include in the dataset retrieved. In this case, we use the special identifier *, which is equivalent to extracting all of the columns available in the dataset. Following the list of columns is the keyword FROM, which is itself followed by a comma-separated list of tables. This statement retrieves data from only one table, the book table.

The format in which the dataset is returned to PHP by the database system depends largely on the system itself and on the extension you are using to access it; for example, the “traditional” MySQL library returns datasets as resources from which you can extract individual rows in the form of arrays. Newer libraries, on the other hand, tend to encapsulate result sets in objects.

You will rarely need to gain access to all of the records in a table—after all, relational databases are all about organizing data and making it easily searchable. Therefore, you will most often find yourself limiting the rows returned by a SELECT statement using a WHERE clause. For example, for thebook table, you may wish to retrieve all books written by a specific author. This is possible using WHERE:

SELECT * FROM book WHERE author = 'Ray Bradbury';

The recordset returned by this SELECT statement will contain all books written by the author specified in the WHERE clause (assuming, of course, that your naming convention is consistent). You may also list more than one parameter in a WHERE clause to further limit or broaden the results, using a number of logical conjunctions:

SELECT *

    FROM book

    WHERE author = 'Ray Bradbury'

        OR author = 'George Orwell';

SELECT *

  FROM book

  WHERE author = 'Ray Bradbury'

        AND publisher LIKE '%Del Ray';

The first example statement contains an OR clause and, thus, broadens the results to return all books by either author, while the second statement restricts the results with an AND clause specifying all books by the author that were also published by a specific publisher. Note, here, the use of theLIKE operator, which provides a case-insensitive match and allows the use of the % wildcard character to indicate an arbitrary number of characters. Thus, the expression AND publisher LIKE '%Del Ray' will match any publisher that ends in the string del ray, regardless of case.

SQL Joins

As the name implies, joins combine data from multiple tables to create a single recordset. Many applications use extremely complex joins to return recordsets of data spanning many tables. Some of these joins use subqueries with even more joins nested within them. Since joins often comprise very complex queries, they are regarded as an advanced SQL concept and many inexperienced developers try to avoid them, for better or worse. However, they are not as complicated as they are made out to be.

There are two basic types of joins: inner joins and outer joins. In both cases, joins create a link between two tables based on a common set of columns (keys).

Inner Joins

An inner join returns rows from both tables only if keys from both tables can be found that satisfy the join conditions. For example:

SELECT *

   FROM book

   INNER JOIN book_chapter ON book.isbn = book_chapter.isbn;

As you can see, we declare an inner join that creates a link between book and book_chapter; rows are returned only if a common value for the isbn column can be found for both tables.

Note that inner joins only work well with assertive conditions—negative conditions often return bizarre results. For example:

SELECT *

  FROM book

  INNER JOIN book_chapter ON book.isbn <> book_chapter.isbn;

You would probably expect this query to return a list of all the records in the book table that do not have a corresponding set of records in book_chapter. However, the database engine actually returns a dataset that contains an entry for each record in book_chapter that does not match each record inbook; the end result is a dataset that contains every line in book_chapter repeated many times over (the actual size of the set depending on the number of rows between the two tables that do have matching values for their respective isbn columns).

Outer Joins

Where inner joins restrict the results returned to those that match records in both tables, outer joins return all records from one table while restricting the other table to matching records, which means that some of the columns in the results will contain NULL values. This is a powerful, yet sometimes confusing, feature of SQL database engines.

Left joins are a type of outer join in which every record in the left table that matches the WHERE clause (if there is one) will be returned regardless of a match made in the ON clause of the right table. For example, consider the following SQL statement with a LEFT JOIN clause:

SELECT book.title, author.last_name

    FROM author

    LEFT JOIN book ON book.author_id = author.id;

The table on the left is the author table because it is the table included as the primary table for the statement in the FROM clause. The table on the right is the book table because it is included in the JOIN clause. Since this is a LEFT JOIN and there is no further WHERE clause limiting the results, all records from the author table will be in the returned results. However, only those records from the book table that match the ON clause where book.author_id = author.id will be among the results.

Author table

id

last_name

1

Bradbury

2

Asimov

3

Martin

4

Rowling

5

Hornby

Book table

id

title

author_id

1

I, Robot

2

2

A Storm of Swords

3

3

Fever Pitch

5

4

About a Boy

5

Join Results

title

last_name

Bradbury

 

Asimov

I, Robot

Martin

A Storm of Swords

Rowling

 

Hornby

Fever Pitch

Hornby

About a Boy

Right joins are analogous to left joins, only reversed: instead of returning all results from the “left” side, the right join returns all results from the “right” side, restricting results from the “left” side to matches of the ON clause. Beware, however, that the type of join used will impact the data returned, so be sure to use the correct type of join for the job.

The following SQL statement performs a task similar to that shown in the left join example. However, the LEFT JOIN clause has been replaced with a RIGHT JOIN clause:

SELECT book.title, author.last_name

    FROM author

    RIGHT JOIN book ON book.author_id = author.id;

Here, the table on the left is still the author table, and the right table is still the book table, but, this time, the results returned will include all records from the book table and only those from the author table that match the ON clause where book.author_id = author.id.

Advanced Database Topics

It is difficult to provide a discussion that deals with specific advanced topics because the creators of the exam decided to stick with standard SQL-92, and most of the advanced features are, in fact, implemented individually by each database vendor as extensions to the standard language, and they are incompatible among each other.

Still, there are two topics that deserve particular mention: transactions and prepared statements.

Transactions

Many database engines implement transaction blocks, which are groups of operations that are committed (or discarded) atomically, so that either all of them are applied to the database, or none. Database engines that implement transactions are often said to be ACID-compliant. That is, they offer atomicity, consistency, isolation, and durability, or ACID. This ensures that any work performed during a transaction will be applied safely to the database when it is committed. Transactions may also be undone, or rolled back, before they are committed, allowing you to implement error checking and handling before data is applied to the database.

A transaction begins with a START TRANSACTION statement. From here on, all further operations take place in a sandbox that does not affect any other user—or, indeed, the database itself—until the transaction is either completed using the COMMIT statement or undone using ROLLBACK. If any of the statements in the block fail for any reason, the entire transaction block will fail; none of the changes will be made to the database. Alternatively, the ROLLBACK statement may be used to discard any changes made since the transaction block began.

Here are two examples:

START TRANSACTION;

DELETE FROM book WHERE isbn LIKE '0655%';

UPDATE book_chapter SET chapter_number = chapter_number + 1;

ROLLBACK;

START TRANSACTION;

UPDATE book SET id = id + 1;

DELETE FROM book_chapter WHERE isbn LIKE '0433%';

COMMIT;

The first transaction block will essentially cause no changes to the database, since it ends with a rollback statement. Keep in mind that this condition usually takes place in scenarios in which multiple operations are interdependent and must all succeed in order for the transaction to be completed. Typically, this concept is illustrated with the transfer of money from one bank account to another: the transaction, in this case, isn’t complete until the money has been taken from the source account and deposited in the destination account. If, for any reason, the second part of the operation isn’t possible, the transaction is rolled back so that the money doesn’t just disappear.

Prepared Statements

For the most part, the only thing that changes in your application’s use of SQL is the data in the queries you pass along to the database system; the queries themselves almost never do. This means at the very least that your database system has to parse and compile the SQL code that you pass along every time. While this is not a large amount of overhead, it does add up—not to mention the fact that you do need to ensure that you escape all of your data properly every time.

Many modern database systems allow you to short-circuit this process by means of a technique known as a prepared statement. A prepared statement is, essentially, the template of an SQL statement that has been pre-parsed and compiled and is ready to be executed by passing it the appropriate data. Each database system implements this in a different way, but, generally speaking, the process works in three steps: first, you create the prepared statement, replacing your data with a set of markers such as question marks or named entities. Next, you load the data into the statement, and finally, you execute it. This process allows you to avoid mixing data and SQL code in the same string, which reduces the opportunity for improper escaping and, therefore, for security issues caused by malicious data.

Working with Databases

Now that you have a basic understanding of SQL, including how to retrieve, modify, and delete data; join tables to retrieve a consolidated recordset; and use transaction blocks and prepared statements, it’s time to learn how to interact with an SQL database engine. PHP provides many ways to connect to different database engines. One way to access many databases through a single interface is PHP Data Objects, or PDO. Another way is through the native driver functions for a specific database. Since the exam includes questions covering both PDO and the MySQL Improved Extension (mysqli), we will briefly cover the use of PDO and mysqli to accomplish the SQL tasks listed earlier in this chapter.

New in PHP 5.5: The ext/mysql extension has been officially deprecated and should be avoided for all new code. We recommend using PDO as the alternative.

PHP Data Objects (PDO)

The standard distribution of PHP 5.1 and greater includes PDO and the drivers for SQLite by default. However, there are many other database drivers for PDO, including Microsoft SQL Server, Firebird, MySQL, Oracle, PostgreSQL, and ODBC. Refer to the PDO documentation on the PHP website for details on how to install each of these drivers. Once the driver is installed, the process for using it is, for the most part, the same because PDO provides a unified data access layer to each of these engines. There is no longer a need for separate mysql_query() or pg_query() functions, as PDO provides a single object-oriented interface to all of these databases.

The difference comes in the SQL used for each database; each engine provides its own specialized keywords, and PDO does not provide a means for standardizing statements across database engines. Thus, when switching an application from one database to another, you will need to pay careful attention to the SQL statements issued from your application to ensure they do not contain keywords or functionality that the new database engine does not recognize.

Connecting to a Database with PDO

To connect to a database, PDO requires at least a Data Source Name, or DSN, formatted according to the driver used. Detailed DSN formatting documentation for each driver can be found on the PHP website. Additionally, if your database requires a username or password, PDO will also need these to access the database. A sample connection to a MySQL database using the library database described earlier might look like this:

$dsn = 'mysql:host=localhost;dbname=library';

$dbh = new PDO($dsn, 'dbuser', 'dbpass');

Since each of these examples assumes a MySQL database, it’s worth mentioning here that the MySQL client library contains a few quirks that cause some irregularities when using the PDO_MYSQL driver, specifically with regard to prepared statements. To resolve these irregularities, it is necessary to set a PDO attribute after connecting to the database. The following line of code will set PDO to use its own native query parser for prepared statements instead of the MySQL client library API:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

For the remainder of this chapter, you’ll notice the use of try/catch statements (described in the Errors and Exceptions chapter). This is not only a best practice; it is also very useful in debugging. Note that the default error mode for PDO is PDO::ERRMODE_SILENT, which means that it will not emit any warnings or error messages. For the examples in this chapter, however, the error mode is set to PDO::ERRMODE_EXECEPTION. This causes PDO to throw a PDOExecption when an error occurs. This exception can be caught and displayed for debugging purposes. The following illustrates this setup; assume that all code examples replace the comment:

Listing 7.1: Database connection

try {

   $dsn = 'mysql:host=localhost;dbname=library';

   $dbh = new PDO($dsn, 'dbuser', 'dbpass');

   $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

   $dbh->setAttribute(PDO::ATTR_ERRMODE,

                      PDO::ERRMODE_EXCEPTION);

   // All other database calls go here

} catch (PDOException $e) {

    echo 'Failed: ' . $e->getMessage();

}

Querying the Database with PDO

To retrieve a result set from a database using PDO, use the PDO::query() method. To escape a value included in a query (e.g., from $_GET, $_POST, $_COOKIE, etc.) use the PDO::quote() method. PDO will ensure that the string is quoted properly for the database used.

Not all database drivers for PDO implement the PDO::quote() method. For this reason, and to ensure the best possible approach to security, it is best to use prepared statements and bound parameters, described in the next section.

Listing 7.2: Quoting values

// Filter input from $_GET

$author = "";

if (ctype_alpha($_GET['author'])) {

    $author = $_GET['author'];

}

// Escape the value of $author with quote()

$sql = 'SELECT author.*, book.* FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = ' . $dbh->quote($author);

// Execute the statement and echo the results

$results = $dbh->query($sql);

foreach ($results as $row) {

    echo "{$row['title']}, {$row['last_name']}\n";

}

The method PDO::query() returns a PDOStatement object. By default, the fetch mode for a PDOStatement is PDO::FETCH_BOTH, which means that it will return an array containing both associative and numeric indexes. It is possible to change the PDOStatement object to return, for example, an object instead of an array so that each column in the result set may be accessed as properties of an object instead of array indices.

$results = $dbh->query($sql);

$results->setFetchMode(PDO::FETCH_OBJ);

foreach ($results as $row) {

  echo "{$row->title}, {$row->last_name}\n";

}

To execute an INSERT, UPDATE, or DELETE statement against a database, PDO provides the PDO::exec() method, which executes an SQL statement and returns the number of rows affected.

$sql = "

INSERT INTO book (isbn, title, author_id, publisher_id)

  VALUES ('0395974682', 'The Lord of the Rings', 1, 3)";

$affected = $dbh->exec($sql);

echo "Records affected: {$affected}";

These are very simple ways to query and execute statements against a database using PDO, but PDO provides much more power and functionality beyond simple query and execute methods, as well as the ability to use prepared statements and bound parameters even if the database engine itself does not support these features.

Prepared Statements and Bound Parameters with PDO

A prepared statement is an SQL statement that has been prepared for either immediate or delayed execution. With PDO, you may prepare a statement for execution and reuse the same statement multiple times throughout the lifetime of a running script. If a database does not support prepared statements, PDO will internally emulate the functionality. If a database driver does support prepared statements, however, PDO will use the native database functionality for prepared statements, improving the performance of your application, since most database engines internally cache prepared statements for reuse.

In the following code, $stmt has been prepared and may now be used multiple times throughout the script. This example does not make apparent the value of prepared statements, but it does illustrate the general format for creating a prepared statement:

Listing 7.3: A prepared statement

$stmt = $dbh->prepare($sql);

$stmt->setFetchMode(PDO::FETCH_OBJ);

$stmt->execute();

$results = $stmt->fetchAll();

foreach ($results as $row) {

    echo "{$row->title}, {$row->last_name}\n";

}

The great value of prepared statements is in their use of bound parameters. Preparing statements intended for reuse with different parameter values will improve the performance of your application and mitigate the risk of SQL injection attacks since there is no need to manually quote the parameters with PDO::quote().

The following code uses the same prepared SQL statement for two separate queries. For each query, it binds a parameter to a named placeholder (:author). While this example uses named placeholders—a named “template” variable preceded by a colon (:)—it is also possible to use question mark placeholders.

Listing 7.4: A prepared statement with named placeholders

// Filter input from $_GET

$author1 = "";

if (ctype_alpha($_GET['author1'])) {

    $author1 = $_GET['author1'];

}

$author2 = "";

if (ctype_alpha($_GET['author2'])) {

    $author2 = $_GET['author2'];

}

// Set a named placeholder in the SQL statement for author

$sql = 'SELECT author.*, book.* FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = :author';

$stmt = $dbh->prepare($sql);

$stmt->setFetchMode(PDO::FETCH_OBJ);

// Fetch results for the first author

$stmt->bindParam(':author', $author1);

$stmt->execute();

$results = $stmt->fetchAll();

foreach ($results as $row) {

    echo "{$row->title}, {$row->last_name}\n";

}

// Fetch results for the second author

$stmt->bindParam(':author', $author2);

$stmt->execute();

$results = $stmt->fetchAll();

foreach ($results as $row) {

    echo "{$row->title}, {$row->last_name}\n";

}

The following illustrates the use of question mark placeholders instead of named placeholders:

Listing 7.5: A prepared statement with question mark placeholders

// Filter input from $_GET

$author1 = "";

if (ctype_alpha($_GET['author1'])) {

    $author1 = $_GET['author1'];

}

$sql = 'SELECT author.*, book.*

        FROM author

          LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = ?';

$stmt = $dbh->prepare($sql);

$stmt->bindParam(1, $author1, PDO::PARAM_STR, 20);

Prepared statements with bound parameters are perhaps among the most useful and powerful features of PDO.

Transactions With PDO

For databases that natively support transactions, PDO implements transaction functionality with the PDO::beginTransaction(), PDO::commit(), and PDO::rollBack() methods. PDO does not try to emulate transactions for those database engines that do not support them. See the Transactions section earlier in this chapter for more information on how transactions work.

The following code again shows the full example, including the try/catch statements. If any of the statements executed against the database fail, then PDO will throw an exception. When catch catches the the exception, you can call PDO::rollBack() to ensure that any actions taken during the transaction are rolled back. Here, one of the INSERT statements fails to list all columns for which it has values. Thus, it throws an exception and the valid INSERT statement executed earlier is not committed to the database.

Listing 7.6: Database transaction

try {

    $dsn = 'mysql:host=localhost;dbname=library';

    $dbh = new PDO($dsn, 'dbuser', 'dbpass');

    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

    $dbh->setAttribute(PDO::ATTR_ERRMODE,

                       PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();

    $dbh->exec("

        INSERT INTO book

            (isbn, title, author_id, publisher_id)

        VALUES

            ('0395974682', 'The Lord of the Rings', 1, 3)");

    $dbh->exec("INSERT INTO book (title)

                  VALUES ('Animal Farm', 3, 2)");

    $dbh->commit();

} catch (PDOException $e) {

    $dbh->rollBack();

    echo 'Failed: ' . $e->getMessage();

}

MySQL Improved Extension (mysqli)

PHP and MySQL have developed a close relationship over the years; when MySQL introduced new features such as transactions, prepared statements, and a more efficient client library API, PHP introduced the MySQL improved (or mysqli) extenstion. The mysqli extension provides access to the features of MySQL 4.1.3 and later and has been available since PHP 5. Since mysqli provides prepared statements, bound parameters, and transactions, among other advanced features, it is an important and essential database driver for any programmer using PHP 5 and MySQL 4.1.3 or later.

The mysqli extension provides both a procedural approach for those who are accustomed to using the now deprecated mysql_* functions and an object-oriented interface for those interested in and comfortable with object-oriented programming (OOP). For this reason, I will show both procedural and OOP examples when demonstrating database concepts with mysqli. This section appears similar to that of the previous PDO section, since the topics covered are the same, but all examples and discussion use mysqli to illustrate the concepts.

Connecting to a Database with mysqli

mysqli does not require any parameters to connect to a database. If it is not passing any parameters, it assumes that it is connecting to localhost. Parameters that may be passed are host, username, password, database name, port, and socket. Whenever possible, mysqli will attempt to use Unix sockets to connect to the database rather than TCP/IP.

A sample connection to a MySQL database using the library database described earlier might look like the following code snippet, which uses msyqli’s object-oriented interface:

Listing 7.7: Connecting with mysqli

$mysqli = new mysqli(

    'localhost', 'dbuser', 'dbpass', 'library'

);

if (mysqli_connect_errno()) {

    echo 'Connect failed: ' . mysqli_connect_error();

    exit;

}

// All other database calls go here

$mysqli->close();

The same connection using a procedural approach instead of OOP might look like this:

Listing 7.8: Connecting with mysqli, procedural functions

$dbh = mysqli_connect(

    'localhost', 'dbuser', 'dbpass', 'library'

);

if (!$dbh) {

    echo 'Connect failed: ' . mysqli_connect_error();

    exit;

}

// All other database calls go here

mysqli_close($dbh);

Note that there are not many differences, the main one being the use of the database resource in the subsequent mysqli_* function calls, such as in mysqli_close().

Earlier, with PDO, you may have noticed the use of try/catch statements to catch exceptions thrown from PDO. Since mysqli does not throw exceptions, the try/catch blocks are not present in these examples. Instead, after attempting to make a database connection, you’ll note the use ofmysqli_connect_error(). This function checks for any error that might have occurred when attempting to connect to the database. In this case, the script simply echoes the error and exits. Later, you’ll see how to check for errors when querying the database.

Since mysqli provides the mysqli class through its object-oriented interface, it is possible to extend this class, if desired, and cause it to throw exceptions on errors, as well as provide other functionality to the child class.

For all other examples in this section, replace the // All other database calls go here message with the example code.

Querying the Database with mysqli

To retrieve a result set from a database using mysqli, you may use the mysqli::real_query() (for the OOP approach) or mysqli_real_query() (for the procedural approach) methods. To escape a value included in a query (e.g., from $_GET, $_POST, $_COOKIE, etc.) use the mysqli::real_escape_string() ormysqli_real_escape_string() methods. With these escape methods, mysqli will ensure that the string is quoted properly for the database, taking into account the database’s current character set. See the Security chapter chapter for more discussion of escaping strings for database queries.

The mysqli extension also provides the simpler mysqli::query() and mysqli_query() methods, which will immediately return a result set. With mysqli::real_query() or mysqli_real_query() the result set is not returned until mysqli::store_result(), mysqli_store_result(), mysqli::use_result(), or mysqli_use_result() are called. Using the *real_query methods is beneficial, however, since these methods allow you to call stored procedures and work with buffered queries. The following examples for querying the database use the *real_query methods, beginning with an OOP example:

Listing 7.9: Query methods

// Filter input from $_GET

$author = '';

if (ctype_alpha($_GET['author'])) {

  $author = $_GET['author'];

}

// Escape the value of $author with mysqli->real_escape_string()

$sql = 'SELECT author.*, book.* FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = "'

        . $mysqli->real_escape_string($author) .'"';

// Execute the statement and echo the results

if (!$mysqli->real_query($sql)) {

    echo 'Error in query: ' . $mysqli->error;

    exit;

}

if ($result = $mysqli->store_result()) {

    while ($row = $result->fetch_assoc()) {

        echo "{$row['title']}, {$row['last_name']}\n";

    }

    $result->close();

}

For the procedural style, the code would look like this:

Listing 7.10: Procedural query methods

// Filter input from $_GET

$author = '';

if (ctype_alpha($_GET['author'])) {

  $author = $_GET['author'];

}

// Escape the value of $author with

// mysqli->real_escape_string().

// Unlike quote(), it does not add quotes around

// your string.

$sql = 'SELECT author.*, book.* FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = "'

        . mysqli_real_escape_string($dbh, $author) . '"' ;

// Execute the statement and echo the results

if (!mysqli_real_query($dbh, $sql)) {

    echo 'Error in query: ' . mysqli_error();

    exit;

}

if ($result = mysqli_store_result($dbh)) {

    while ($row = mysqli_fetch_assoc($result)) {

        echo "{$row['title']}, {$row['last_name']}\n";

    }

    mysqli_free_result($result);

}

In both of these examples, the *real_query calls are checked to see whether TRUE or FALSE is returned. If the return value is FALSE, then there was an error with the query, and we echo the error message and exit the program. If the return value is TRUE, the query was executed successfully; however, it could still return zero rows. Also note the use of the *fetch_assoc methods. These methods return an associative array of the result set with values mapped to their column names. You may also use *fetch_row, which returns a numerically indexed array; *fetch_array, which allows you to specify a numeric array, associative array, or both; and *fetch_object, which fetches the current row of the result set into the specified object.

Prepared Statements and Bound Parameters with mysqli

As with PDO, with mysqli you may prepare a statement for execution and reuse the same statement multiple times throughout the lifetime of a running script. Preparing a statement will also cache the statement in the database, thus improving performance since the statement may be reused again and again as long as it remains in the database cache.

In the following code, $stmt has been prepared with the SQL in $sql and may now be used multiple times throughout the script. Also note the use of the *bind_param methods in these examples. Preparing statements intended for reuse with different parameter values will improve the performance of your application and mitigate the risk of SQL injection attacks since there is no need to manually escape the parameters with the *real_escape_string methods.

The following code binds a parameter to a question-mark placeholder (?) and then illustrates the use of bound results by binding the returned value book.title to the $title variable with the *bind_result methods. Even if multiple rows are returned, as you loop through the results, the $title variable contains the title returned for the current row.

Listing 7.11: Bound parameters with mysqli

// Filter input from $_GET

$author = '';

if (ctype_alpha($_GET['author'])) {

    $author = $_GET['author'];

}

// Set a named placeholder in the SQL statement for author

$sql = 'SELECT book.title FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = ?';

if ($stmt = $mysqli->prepare($sql)) {

    $stmt->bind_param('s', $author);

    $stmt->execute();

    $stmt->bind_result($title);

    while ($stmt->fetch()) {

        echo "{$title}, {$author}\n";

    }

    $stmt->close();

}

Again, the same code using the procedural approach looks like this:

Listing 7.12: Bound parameters with mysqli, procedural approach

// Filter input from $_GET

$author = '';

if (ctype_alpha($_GET['author'])) {

    $author = $_GET['author'];

}

// Set a named placeholder in the SQL statement for author

$sql = 'SELECT book.title FROM author

        LEFT JOIN book ON author.id = book.author_id

        WHERE author.last_name = ?';

if ($stmt = mysqli_prepare($dbh, $sql)) {

    mysqli_stmt_bind_param($stmt, 's', $author);

    mysqli_stmt_execute($stmt);

    mysqli_stmt_bind_result($dbh, $title);

    while (mysqli_stmt_fetch()) {

        echo "{$title}, {$author}\n";

    }

    mysqli_stmt_close($stmt);

}

Transactions with mysqli

The mysqli extension implements transaction functionality with the *commit and *rollback methods. By default, mysqli runs in auto-commit mode, which means that each database statement will be committed immediately. To disable this functionality and begin a transaction, set the auto-commit mode to FALSE using the *autocommit methods. Please note that the *autocommit methods will not work with nontransactional table types, such as MyISAM or ISAM. For these table types, all database statements are always committed immediately. See the Transactions section earlier in this chapter for more information on how transactions work.

The following code illustrates the use of transactions with mysqli. If any of the statements executed against the database fail, then the call to mysqli::commit() or mysqli_commit() will return FALSE. In this case, you can call the *rollback methods to ensure that any actions taken during the transaction are rolled back and discarded. Here, one of the INSERT statements fails to list all columns for which it has values. Thus, the commit fails and the valid INSERT statement executed earlier is not committed to the database:

Listing 7.13: Handling transactions with mysqli

$mysqli->autocommit(FALSE);

$mysqli->query(

    "INSERT INTO book

         (isbn, title, author_id, publisher_id)

     VALUES

         ('0395974682', 'The Lord of the Rings', 1, 3)"

);

$mysqli->query(

    "INSERT INTO book (title)

            VALUES ('Animal Farm', 3, 2)"

);

if (!$mysqli->commit()) {

    $mysqli->rollback();

}

The procedural version of the code is very similar:

Listing 7.14: Handling transactions with mysqli, procedural approach

mysqli_autocommit($dbh, FALSE);

mysqli_query(

    $dbh,

    "INSERT INTO book

         (isbn, title, author_id, publisher_id)

     VALUES

         ('0395974682', 'The Lord of the Rings', 1, 3)

");

mysqli_query(

    $dbh,

    "INSERT INTO book (title)

     VALUES ('Animal Farm', 3, 2)"

);

if (!mysqli_commit($dbh)) {

    mysqli_rollback($dbh);

}

MySQL Native Driver

The MySQL Native Driver was introduced with PHP 5.3. The MySQL Native Driver, or mysqlnd, is an alternative to the standard libmysqlclient library that ext/mysql, ext/mysqli, and ext/pdo_mysql all previously wrapped. It works transparently with all three extensions.

The use of mysqlnd is optional; however, it is now the recommended—and default—option, bringing performance and feature gains.

One of the more powerful feature additions of mysqlnd is a plugin architecture, which supports numerous plugins that are available via PECL. These plugins work transparently with all three mysql extensions.

·        mysqlnd_ms: A replication and load-balancing plugin that allows you to do simple load balancing and read-write splitting

·        mysqlnd_memcache: Transparently translates SQL into requests for the MySQL InnoDB Memcached Daemon Plugin

·        mysqlnd_qc: Adds basic client-side result set caching

Summary

This chapter deals with a minimal set of the database functionality that you would typically use for day-to-day programming. In addition, it describes a small set of advanced features that many database engines now provide, including transactions, prepared statements, and bound parameters. These features have become essential to Web application programming, which is why PDO provides unified access to this functionality regardless of the database engine used. In addition to PDO, PHP’s long history with MySQL makes some knowledge of the mysqli extension important to PHP programmers.