As we outlined earlier in this chapter, PDO is a database-independent object-oriented, interface to relational databases for use in PHP 5.x. PDO was officially released with PHP 5.1, although "experimental" versions were available with the 5.0 release. PDO provides a very powerful and easy-to-use syntax, as well as providing good support for MySQL stored programs.

We'll start with a brief review of PDO basics; if you are already familiar with PDO, you might want to skip forward to the section "Calling Stored Programs with PDO" later in this chapter.

13.3.1. Connecting to MySQL

To create a connection to MySQL, we create a database handle using the PDO constructor method. The constructor takes three arguments:

dsn

The "dsn" string represents the database to be connected; it has the form 'mysql:dbname=dbname;host=hostname;port=port_no'.

user

The username to be used for the connection.

password

The password for the user account specified.

This method will throw an exception if the connection cannot be made, so you will normally enclose it in a TRy/catch block. The getMessage( ) method of the PDOException exception will contain details of any problems encountered when establishing the connection.

Example 13-20 shows a connection to MySQL being established.

Example 13-20. Connecting to MySQL using PDO

13.3.2. Executing a Simple Non-SELECT Statement

You can execute a simple one-off statement that does not return a result set (e.g., is not a SELECT, SHOW STATUS, etc.) with the exec( ) method of the database object, as shown in Example 13-21.

Example 13-21. Executing a non-select with PDO

The exec( ) method returns the number of rows returned, as opposed to a success or failure status. Example 13-22 shows a code fragment that uses the return value to determine the number of rows inserted.

Example 13-22. Using the return value from the exec( ) method

13.3.3. Catching Errors

Some PDO methods return a success or failure status, while otherslike $dbh->exec( ) return the number of rows processed. Therefore, it's usually best to check for an error after each statement has executed. The errorCode( ) method returns the SQLSTATE from the most recent execution, while errorInfo( ) returns a three-element array that contains the SQLSTATE, MySQL error code, and MySQL error message.

Example 13-23 checks the errorCode( ) status from the preceding exec( ) call, andif the SQLSTATE does not indicate success (00000)prints the error information from errorInfo( ).

Example 13-23. Using PDO error status methods

The output from Example 13-23 is shown in Example 13-24.

Example 13-24. Output from the errorInfo( ) method

If you want to produce a more succinct error output, you can use the PHP implode( ) function to join the elements of the errorInfo( ) call into a single string, as shown in Example 13-25.

Example 13-25. Generating a succinct error message

13.3.4. Managing Transactions

If you are using a transactional storage engine such as InnoDB, then you can control transactions using the standard MySQL statements such as SET AUTOCOMMIT , START TRANSACTION , COMMIT , and ROLLBACK . However, instead of using these statements, you may want to take advantage of the native methods available in the PDO interface, which allow you to directly control transactions. These methods are applied to the database connection object and include beginTransaction( ), commit( ), and rollback( ).

Example 13-26 illustrates the use of these transaction control methods to implement transaction logic in PDO.

Example 13-26. Using PDO transaction control methods

13.3.5. Issuing a One-Off Query

The query( ) method can be used to generate a one-off query. It returns an object containing the result set returned by the query. Individual columns may be accessed either by column name or column number (using column name is recommended to improve readability and maintainability). Example 13-27 shows a query being executed and the results accessed by column name.

Example 13-27. Issuing a simple query in PDO

In Example 13-28 we retrieve the column results by column number.

Example 13-28. Accessing query results by column number

Using the query( ) method is a convenient way to quickly execute a query, but it is not a good way to execute a query that will be re-executed, and it has less functionality than the prepare( ) and execute( ) methods that we are going to discuss next.

13.3.6. Using Prepared Statements

PDO prepared statements should be used whenever you are going to repetitively execute a statement. The prepare( ) and execute( ) methods also allow you to exercise greater control over statement execution, and they offer some additional capabilities that are particularly important when executing stored procedures.

The prepare( ) method accepts a SQL statement and returns a PDOStatement object. The execute( ) method of the statement can then be used to execute the statement. Example 13-29 shows the use of prepare( ) and execute( ) to execute a simple INSERT statement.

Example 13-29. Prepared statement without result set

If the SQL statement passed to the statement is a query, then we can use the fetch( ) method of the statement to access the result set. Each call to fetch( ) returns an array containing the values for that row. As with the query call, we can access the column values by name or by column number. Example 13-30 shows us accessing the column values by name.

Example 13-30. Retrieving a result set from a prepared statement

13.3.7. Binding Parameters to a Prepared Statement

We usually create prepared statements with the intention of re-executing the statementoften in association with new parameter values.

If you want to re-execute a SQL statement while changing the WHERE clause criteria, DML values, or some other part of the SQL, you will need to include placeholders for substitution variables (sometimes called SQL parameters or bind variables). These are represented in the SQL text by including variable names prefixed by :, or as ? symbols.

We then use the bindParam( ) method to associate PHP variables with the placeholders in the SQL text. This must occur after the prepare( ) method has been called but before the execute( ) method. bindParam( ) requires that you specify the data type of the parameter as a PDO constant (such as PDO::PARAM_INT) andfor certain data types such as stringsa length.

Once we have associated PHP variables with a SQL parameter using bindParam( ), we are ready to execute our SQL. If we wish to re-execute the SQL, we can simply change the values of the PHP variables and re-issue the execute( ) call: we do not have to call bindParam( ) whenever the parameter values change.

Example 13-31 shows how we can bind parameters to a prepared statement.

Example 13-31. Binding parameters to a prepared statement

Let's look at this example line by line:

13.3.8. Getting Result Set Metadata

Sometimes we will need to execute a SQL statement without being sure about the structure of the result set that it might return. This is particularly true of stored programs, which can return multiple result sets in possibly unpredictable ways. We can determine the result set to be returned by a prepared statement by using PDO metadata methods.

The prepared statement object supports a columnCount( ) method, which returns the number of columns to be returned by the prepared statement. getColumnMeta( ) can be called to obtain an array containing details about a specific column such as its name, data type, and length.

Table 13-1 lists the elements contained in the array returned by getColumnMeta( ).

In Example 13-32 we use the getColumnMeta( ) function to retrieve and print names, data types, and lengths of columns returned by a query.

Example 13-32. Obtaining column metadata using the getColumnMeta( ) method

13.3.9. Processing a Dynamic Result Set

Using the columnCount( ) method and (optionally) the getColumnMeta( ) method, we can fairly easily process a result set even if we have no idea what the structure of the result set will be when we code.

Example 13-33 shows a PHP function that will accept any SELECT statement and output an HTML table showing the result set.

Example 13-33. PDO function to generate an HTML table from a SQL statement

Let's step through the code:

Figure 13-2 shows the output generated by the PDO routine for a simple SQL statement that prints some columns from the employees table.

13.3.10. Calling Stored Programs with PDO

All of the PDO methods we've examined so far can be used with stored programs. For instance, you can use the exec( ) method to call a simple stored program that doesn't return a result set, as shown in Example 13-34.

Example 13-34. Calling a simple stored procedure in PDO with the exec( ) method

If the stored procedure returns a single result set, then you have the same choices as for a SELECT statement or another SQL statement that returns a result set. That is, you can use prepare( ) and execute( ) for the statement, or you can use the query( ) method. Generally we advise that you use prepare( ) and execute( ), since these can be more efficient and have greater flexibility. Example 13-35 shows the use of query( ) to retrieve a single result set from a stored procedure.

Example 13-35. Retrieving a single stored procedure result set using the PDO query( ) method

Figure 13-2. Output from PDO dynamic query example

The prepare( ), execute( ), and fetch( ) sequence for retrieving a single result set from a stored procedure is exactly the same as for a SELECT statement. Example 13-36 shows the use of this sequence to retrieve a result set from a stored procedure.

Example 13-36. Retrieving a single stored procedure result set using prepare( ), execute( ), and fetch( )

13.3.11. Binding Input Parameters to Stored Programs

If we use prepare( ) to ready our stored procedure for execution, we can bind parameters to the stored procedure using the bindParam( ) call, just as we have done with standard SQL statements, as shown in Example 13-37.

Example 13-37. Binding parameters to stored procedures

13.3.12. Handling Multiple Result Sets

If a stored procedure returns more than one result set, then you can use the nexTRowset( ) method to move through each result set in sequence. The specific coding technique in PHP depends somewhat on whether you know the exact number and structure of the result sets. For instance, in the case of the very simple stored procedure in Example 13-38, we know that two, and only two, result sets will be returned, and we know the exact structure of each.

Example 13-38. Stored procedure that returns two result sets

To process this stored procedure, we merely need to code fetch( ) loops to retrieve each result set and add a nextrowset( ) call between the first set of fetches and the second. Example 13-39 illustrates this technique.

Example 13-39. Fetching two result sets from a stored procedure in PDO

Of course, we don't always know exactly how many result sets a stored procedure might return, and each result set can have an unpredictable structure. Therefore, we often want to combine the nextrowset( ) method with the getColumnMeta( ) method we saw earlier to dynamically process the result sets that the stored procedure produces. For instance, the stored procedure in Example 13-40 will return different result sets depending on whether the employee is a sales representative or not.

Example 13-40. Stored procedure that returns a variable number of result sets

It's relatively simple to handle variable result set types with varying results. First, we construct a loop that will continue as long as nextrowset( ) returns TRUE. Within that loop we use the getColumnMeta( ) call to retrieve the names and types of columns and then fetch the rows using the methods we discussed previously in the section "Getting Result Set Metadata," earlier in this chapter.

Example 13-41 shows some PDO code that will process the multiple, variable result sets output by the stored procedure shown in Example 13-40. In fact, this code is capable of processing the result sets from any stored procedure specified in the $sql variable.

Example 13-41. PDO code to process multiple result sets from a stored procedure

Let's walk through this example:

13.3.13. Handling Output Parameters

As we discussed in Chapter 3, MySQL stored procedures can include input (IN), output (OUT), and input-output (INOUT) parameters. For instance, the stored procedure shown in Example 13-42 contains an output parameter that will contain the number of customers for a specific sales representative.

Example 13-42. Stored procedure with an OUT parameter

The PDO specification for the bindParam( ) method allows you to identify a parameter that might return an output value by associating the PDO::PARAM_INPUT_OUTPUT constant with the parameter. Example 13-43 shows how we would use this method to retrieve the value of an output parameter from this stored procedure.

Example 13-43. Binding an output parameter in PDO (not implemented at time of writing)

Unfortunately, as we write this chapter, the ability to use bindParam( ) to retrieve output parameters is not implemented in the PDO MySQL driver (Bug# 11638 current as of MySQL 5.0.19). There is every chance, however, that the method will have been implemented by the time you read this book, so please visit the book's web site where we will report on the status of the PDO driver.

Even without the bindParam( ) method, we can extract the value of an output parameter. We can do this by using a user variable to retrieve the value of the output parameter, and then retrieve this value using a simple SELECT statement. Example 13-44 shows how to do this. We use the @customer_count variable to hold the value of the output parameter and then, in a subsequent step, fetch the value of @customer_count using a one-line SELECT.

Example 13-44. Getting the value of an output parameter without bindParam

If the parameter were of type INOUT, we would simply issue a SET statement to set the value before execution and then issue a SELECT statemnet to retrieve the altered value after execution. We showed how to do this with the mysqli driver earlier in this chapter.

13.3.14. A Complete Example

Let's put PDO to use to create a web page that executes a stored procedure and formats the results in HTML. The stored procedure is shown in Example 13-45. This stored procedure generates some useful data about the MySQL server, including the details of currently connected sessions, status variables, and configuration settings for the database. The number and types of result sets varies depending upon the input parameters: if a valid database is provided in the first parameter, a list of objects for that table is returned. The server version is returned in an output parameter.

To help us generate a well-formatted report, the stored procedure outputs a header row for each of the result sets it returns. This header row is issued as a single-row, single-column result set in which the column name is table_header.

Example 13-45. MySQL server status stored procedure

Our PDO example prompts the user to provide login details for a MySQL server, connects to that server, and attempts to execute the stored procedure. Each result set is formatted as an HTML table and the "special" heading rows are formatted as HTML headers. The output parameter that contains the MySQL server version is retrieved and displayed at the commencement of the output. Example 13-46 displays the complete PDO example.

Example 13-46. A complete PDO example

2MySQL Server Statistics

This code uses most of the techniques we have seen in previous examples, as explained next:

Figure 13-3 shows the output from this PHP example.