MySQL Stored Procedure Programming (2009)

Part III. Using MySQL Stored Programs in Applications

Chapter 13. Using MySQL Stored Programs with PHP

The combination of PHP and MySQL is one of the most popular and powerful partnerships in open source web development and is a key component of the LAMP (Linux-Apache-MySQL-PHP/Perl/Python) stack. There are reportedly more than 18 million web sites based on PHP technology (according to http://www.netcraft.com), and the majority of these are using MySQL as the underlying database.

PHP started off as a simple CGI-based processor for amateur web development in the mid-1990s. It borrowed heavily from the Perl language (at the time, the most popular approach for CGI-based dynamic web development), but was more tightly integrated with HTML and—unlike Perl—was designed specifically for web development.

PHP takes a similar approach to dynamic web content as Microsoft's ASP (Active Server Pages) and J2EE's JSP (Java 2 Enterprise Edition Java Server Pages). All of these technologies involve embedding tags into HTML pages (renamed appropriately as PHP, ASP, or JSP pages, of course) that control the dynamic content of the page. In the case of PHP, the tags contain PHP code. The PHP code is executed by the PHP engine, which is usually deployed within the web server (Apache, IIS, etc.) and typically interacts with a database to provide dynamic, data-driven content.

As a language, PHP delivers much of the flexibility and power of the popular Perl open source scripting language and has a wide variety of interfaces to back-end databases. It is probably fair to characterize PHP as having a shallower learning curve than the ASP.NET or J2EE alternatives. Also, since PHP is open source, software licensing costs are, of course, minimal (although many larger enterprises seek support from Zend Corporation or another commercial entity).

In this chapter we will review the use of PHP with MySQL and show how stored programs can be used within PHP-based applications.

Options for Using MySQL with PHP

PHP currently offers multiple ways of working with MySQL. Some of the more popular methods include:

PEAR (PHP Extension and Application Repository) DB package

This package offers a database-independent API for communicating with relational databases from PHP. PEAR::DB includes support for MySQL, but provides only rudimentary support for MySQL stored programs.

PHP MySQL extension (ext/mysql)

This PHP extension provides MySQL-specific support for working with MySQL. However, the mysql extension does not include methods for working with advanced MySQL features introduced in MySQL 4.1 and 5.0 and will probably never provide direct support for stored programs.

mysqli interface (ext/mysqli)

This PHP extension was introduced to support new features in MySQL 4.1 and 5.0.

PDO (PHP Data Objects)

PDO is a database-independent interface that will probably become the successor to the PEAR::DB interface. PDO became an officially supported interface only in PHP 5.1, so it is the newest of the PHP database interfaces.

Only the mysqli and PDO extensions provide full support for MySQL stored programs. In this chapter we will show how each can be used to interface with MySQL and how to use MySQL stored programs.

Using PHP with the mysqli Extension

Before we look at how to invoke stored programs using PHP and the mysqli extension, let's look at how we perform operations in PHP involving simple SQL statements. These operations will form the foundation for using stored programs in PHP. If you already feel very familiar withmysqli, you might want to skip forward to "Calling Stored Programs with mysqli," later in this chapter.

Enabling the mysqli Extension

The mysqli extension ships as standard with PHP 5.0 and above, but you may need to enable it. You do this by ensuring that ext_mysqi is listed in the extensions section of your php.ini file. The ext_mysqli extension should be included in your default php.ini file, but may be commented out. In Windows, it can be found in the Windows extension section. The relevant line will look something like this:

extension=php_mysqli.dll

On Unix or Linux, the line should look like:

extension=mysqli.so

Connecting to MySQL

To connect to MySQL we first create an object representing a connection using the mysqli call. The mysqli call takes arguments containing the hostname, username, password, database, and port number. The mysqli_connect_errno( ) call will contain any error code associated with the connection, and mysqi_connect_error( ) will contain the error text.

In Example 13-1 we create an object—$mysqli—representing a MySQL connection, and check for any error condition.

Example 13-1. Creating a mysqli connection

# Create a connection

<?php

$mysqli = new mysqli("localhost", "root", "secret", "test");

if (mysqli_connect_errno( )) {

printf("Connect failed: %s\n", mysqli_connect_error( ));

exit ( );

} else {

printf("Connect succeeded\n");

}

?>

Checking for Errors

The mysqli connection object includes properties that reflect any error condition associated with the most recent operation. These properties include:

errno

Contains the MySQL-specific error code

sqlstate

Contains the ANSI SQLSTATE error code

error

Contains the text of the most recent error

When we are using prepared statements (see the section "Using Prepared Statements" later in this chapter), similar properties can be accessed as part of the statement object.

Although PHP 5 supports Java- or C#-style exception handling, the mysqli classes do not currently throw exceptions, so it is usually necessary to check these error codes after every operation.

There are a couple of different common styles for error checking. First, we could check to see if the mysqli call returned TRUE (1) or FALSE (0):

if ($mysqli->query($sql) <> TRUE) {

printf("Statement failed %d: (%s) %s\n"

,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

If we wanted to make our code very compact, we could do this using an "or" statement, as in this example:

$mysqli->query($sql) or printf("Statement failed %d: (%s) %s\n"

,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

Unfortunately, this technique is not very reliable, as there are some mysqli methods that return the number of rows affected, rather than TRUE or FALSE. For these calls, you need to explicitly check the value of $mysqli->errno after the calls, as follows:

$mysqli->query($sql);

if ($mysqli->errno <> 0 ) {

printf("Statement failed %d: (%s) %s\n"

,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

It is probably wiser to explicitly check the value of errno after key method calls so that you can use a consistent style of coding and can avoid introducing bugs that may occur if you misinterpret a method that returns no rows as having encountered an error.

Executing a Simple Non-SELECT Statement

To issue a "one-off" statement that returns no result set, we can use the query method of the mysqli connection object. Example 13-2 provides an example of issuing a simple, one-off statement.

Example 13-2. Issuing a simple statement in mysqli

$mysqli->query("CREATE TABLE guy_1 (guys_integers INT)");

if ($mysqli->errno <> 0 ) {

printf("Statement failed %d: (%s) %s\n"

,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

Retrieving a Result Set

If the statement issued from the query object returns a result set, we can retrieve the rows using the fetch_object( ) method. This method returns a row object, from which we can retrieve the values of the columns returned. Example 13-3 shows us cycling through the results of a query.

Example 13-3. Retrieving a result set from a simple query

$sql="SELECT employee_id, surname, salary

FROM employees

WHERE salary>95000

AND department_id=1

AND status='G'";

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

if ($mysqli->errno) { die ($mysqli->errno." ".$mysqli->error); }

while($row=$results->fetch_object( )) {

printf("%d\t%s\t%d\n",$row->employee_id,$row->surname,$row->salary);

}

An alternative to the fetch_object( ) method is the fetch_row( ) method, in which columns can be referenced by number rather than name. Example 13-4 illustrates this technique.

Example 13-4. Retrieving a result set using fetch_row

$sql="SELECT employee_id, surname, salary

FROM employees

WHERE salary>95000

AND department_id=1

AND status='G'";

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

if ($mysqli->errno) { die ($mysqli->errno." ".$mysqli->error); }

while($row=$results->fetch_row( )) {

printf("%d\t%s\t%d\n",$row[0],$row[1],$row[2]);

}

The use of fetch_row( ) results in code that is harder to read and maintain and is not generally recommended. However, as we shall soon see, the use of fetch_row( ) is convenient when you don't know what the result set will look like when you are writing your code (for instance, when processing a dynamic SQL statement).

Managing Transactions

As with most of the programmatic interfaces to MySQL, you are always free to manage transactions by executing the usual MySQL statements—for example, 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 mysqli interface. These methods can assist with managing transactions and can be more convenient and result in simpler code. Of course, these statements are only meaningful if you are using a transactional storage engine such as InnoDB.

The following methods of the mysqli object (illustrated in Example 13-5) are transaction-oriented:

autocommit( )

Enables or disables the autocommit setting for the current connection

commit( )

Issues a COMMIT of the transaction

rollback( )

Issues a (you guessed it) rollback of the transaction

Example 13-5. Using mysqli transaction-handling methods

$mysqli->autocommit(FALSE);

$mysqli->query("UPDATE account_balance

SET balance=balance-$tfer_amount

WHERE account_id=$from_account");

if ($mysqli->errno) {

printf("transaction aborted: %s\n",$mysqli->error);

$mysqli->rollback( );

}

else {

$mysqli->query("UPDATE account_balance

SET balance=balance+$tfer_amount

WHERE account_id=$to_account");

if ($mysqli->errno) {

printf("transaction aborted: %s\n",$mysqli->error);

$mysqli->rollback( );

}

else {

printf("transaction succeeded\n");

$mysqli->commit( );

}

}

Using Prepared Statements

For SQL statements that may be re-executed, you can use the mysqli prepared statement interfaces. By preparing a statement before execution, you reduce the overhead of re-executing the statement. Furthermore, if a statement contains variable parameters, using the prepare and executecalls is safer than appending these parameters to the SQL and executing, since SQL code cannot be "injected" into prepared statement parameters (see Chapter 18 for a discussion of the security implications of SQL injection).

To create a prepared statement, we use the prepare( ) method of the mysqli interface, which returns a mysqli_stmt object. Any parameters within the prepared statement should be represented by ? characters, which can then be associated with PHP variables through the bind_param( ) method.

Example 13-6 illustrates the process of preparing a statement, binding parameters, and repeatedly executing a SQL statement.

Example 13-6. Preparing and multi-executing a simple SQL statement

1 #Preparing the statment

2 $insert_stmt=$mysqli->prepare("INSERT INTO x VALUES(?,?)")

3 or die($mysqli->error);

4 #associate variables with the input parameters

5 $insert_stmt->bind_param("is", $my_number,$my_string); #i=integer

6 #Execute the statement multiple times....

7 for ($my_number = 1; $my_number <= 10; $my_number++) {

8 $my_string="row ".$my_number;

9 $insert_stmt->execute( ) or die ($insert_stmt->error);

10 }

11 $insert_stmt->close( );

The relevant sections of this code are shown here:

Retrieving Result Sets from Prepared Statements

To retrieve a result set from a prepared statement, we must first associate the columns in the result set with the PHP variables that will hold their values. This is done using the bind_result( ) method of the prepared statement object. We then use the fetch( ) method of the prepared statement to retrieve each row. Example 13-7 illustrates this technique.

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

$sql="SELECT employee_id,surname,firstname

FROM employees

WHERE department_id=?

AND status=?

LIMIT 5";

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

if ($mysqli->errno<>0) {die($mysqli->errno.": ".$mysqli->error);}

$stmt->bind_param("is",$input_department_id,$input_status) or die($stmt-error);

$stmt->bind_result( $employee_id,$surname,$firstname) or die($stmt->error);

$input_department_id=1;

$input_status='G';

$stmt->execute( );

if ($mysqli->errno<>0) {die($stmt.errno.": ".$stmt->error) ;}

while ($stmt->fetch( )) {

printf("%s %s %s\n", $employee_id,$surname,$firstname);

}

Getting Result Set Metadata

If we don't know in advance the structure of the result set being returned by our query, we can use the result_metadata( ) method of the prepared statement to retrieve the column definitions. This method returns a result object that can be queried to return the names, lengths, and types of the columns to be returned.

Example 13-8 shows us retrieving the structure of a result set from a prepared statement.

Example 13-8. Retrieving metadata from a prepared statement

$metadata = $stmt->result_metadata( );

$field_cnt = $metadata->field_count;

while ($colinfo = $metadata->fetch_field( )) {

printf("Column: %s\n", $colinfo->name);

printf("max. Len: %d\n", $colinfo->max_length);

printf("Type: %d\n\n", $colinfo->type);

}

Processing a Dynamic Result Set

Sometimes we need to process a SQL statement without knowing exactly what the columns in the result set will be. In these cases, we can use the result_metadata( ) interface to determine the composition of the result set and dynamically bind the resulting columns. However, the process is not exactly intuitive. Example 13-9 provides some PHP code that will produce an HTML table based on an arbitrary SELECT statement.

Example 13-9. Processing a dynamic result set

1 require_once "HTML/Table.php";

2 $table =new HTML_Table('border=1');

3

4 $stmt=$mysqli->prepare($sql);

5 if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}

6

7 # Retrieve meta-data and print table headings

8 $metadata = $stmt->result_metadata( );

9 $field_cnt = $metadata->field_count;

10 $colnames=array( );

11 while ($colinfo = $metadata->fetch_field( )) {

12 array_push($colnames,$colinfo->name);

13 }

14 $table->addRow($colnames);

15 $table->setRowAttributes(0,array("bgcolor" => "silver"));

16

17

18 # Declare an array to receive column data

19 $stmt_results=array_fill(0,$field_cnt,'');

20 # Set first element of the bind_result parameter as the statement handle

21 $bind_result_parms[0]=$stmt;

22 # Add the references to the column arrays to the parameter list

23 for ($i=0;$i<$field_cnt;$i++) {

24 array_push($bind_result_parms, &$stmt_results[$i]);

25 }

26 #Pass the array to the bind_result function

27 call_user_func_array("mysqli_stmt_bind_result", $bind_result_parms);

28 $stmt->execute( );

29 $row=0;

30 while($stmt->fetch( )) {

31 $row++;

32 for ($i=0;$i<$field_cnt;$i++) {

33 $table->setCellContents($row,$i,$stmt_results[$i]);

34 }

35 }

36 $stmt->close( );

37 print $table->toHtml( );

Let us step through this rather complicated example:

The procedure for rendering the results of dynamic SQL in mysqli is more complicated than we would like. However, the technique outlined above can be used when we do not know in advance what the SQL is or what result set it will output—and this can be particularly important when dealing with stored procedures, since they may return an unpredictable result set sequence.

Figure 13-1 shows the output produced by Example 13-9 when provided with a simple query against the departments table.

Calling Stored Programs with mysqli

All of the mysqli methods for calling standard SQL statements can also be used to call stored programs. For instance, in Example 13-10, we call a stored procedure that does not return a result set using the query method.

Example 13-10. Calling a stored procedure without a result set in mysqli

$sql = 'call simple_stored_proc( )';

$mysqli->query($sql);

if ($mysqli->errno) {

die("Execution failed: ".$mysqli->errno.": ".$mysqli->error);

}

else {

printf("Stored procedure execution succeeded\n");

}

If the stored procedure returns a single result set, we can retrieve the result set as for a SELECT statement by using the fetch_object( ) method. Example 13-11 shows such a simple stored procedure.

Figure 13-1. Sample output from the dynamic SQL PHP routine

Example 13-11. Stored procedure with a single result set

CREATE PROCEDURE department_list( )

READS SQL DATA

SELECT department_name,location from departments;

Example 13-12 shows how we would retrieve the result set from this stored procedure call using query( ) and fetch_object( ).

Example 13-12. Retrieving a result set from a stored procedure

$sql = "call department_list( )";

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

if ($mysqli->errno) {

die("Execution failed: ".$mysqli->errno.": ".$mysqli->error);

}

while ($row = $results->fetch_object( )) {

printf("%s\t%s\n", $row->department_name, $row->location);

}

You will often want to execute the same stored procedure multiple times—possibly with varying input parameters—so it is a best practice to use mysqli prepared statements. We can use prepared statements with stored procedure in pretty much the same way as we would for any other SQL statement. For instance, in Example 13-13, we see a stored procedure that accepts an input parameter and generates a result set based on the value of that input parameter.

Example 13-13. Stored procedure with result set and input parameter

CREATE PROCEDURE customers_for_rep(in_sales_rep_id INT)

READS SQL DATA

SELECT customer_id,customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

We can create a prepared statement for this stored procedure and use the bind_param( ) method to associate the stored procedure input parameter with a PHP variable. Example 13-14 illustrates this technique.

Example 13-14. Using a prepared statement to execute a stored procedure with input parameter and result set

1 $sql = "CALL customers_for_rep(?)";

2 $stmt = $mysqli->prepare($sql);

3 if ($mysqli->errno) {die($mysqli->errno.":: ".$mysqli->error);}

4

5 $stmt->bind_param("i", $in_sales_rep_id);

6 $in_sales_rep_id = 1;

7 $stmt->execute( );

8 if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}

9

10 $stmt->bind_result($customer_id,$customer_name);

11 while ($stmt->fetch( )) {

12 printf("%d %s \n", $customer_id,$customer_name);

13 }

Let's look at this example line by line:

Handling Output Parameters

The mysqli extension does not currently include a method for directly retrieving output parameters from a stored program. However, it is relatively easy to work around this limitation by using a user variable to hold the output parameter and then using a simple SQL statement to retrieve that value. Example 13-15 shows a stored procedure that returns the number of customers for a specific sales representative as a stored procedure output variable.

Example 13-15. Stored procedure with an output parameter

CREATE PROCEDURE sp_rep_customer_count(

in_emp_id DECIMAL(8,0),

OUT out_cust_count INT)

NOT DETERMINISTIC READS SQL DATA

BEGIN

SELECT count(*)

INTO out_cust_count

FROM customers

WHERE sales_rep_id=in_emp_id;

END;

To retrieve the output parameter from this stored procedure, we specify a user variable (see Chapter 3 for a description of user variables) to hold the value of the output parameter, and then we issue a simple SELECT statement to retrieve the value. Example 13-16 illustrates the technique.

Example 13-16. Retrieving the value of an output parameter in mysqli

$sql="CALL sp_rep_customer_count(1,@customer_count)";

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

if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}

$stmt->execute( );

if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}

$stmt->close( );

$results = $mysqli->query("SELECT @customer_count AS customer_count");

$row = $results->fetch_object( );

printf("Customer count=%d\n",$row->customer_count);

Retrieving Multiple Result Sets

If a stored procedure returns more than one result set, then you can use mysqli's multi_query( ) method to process all the results. 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-17, we know that two, and only two, result sets will be returned, and we know the exact structure of each.

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

CREATE PROCEDURE stored_proc_with_2_results(in_sales_rep_id INT)

DETERMINISTIC READS SQL DATA

BEGIN

SELECT employee_id,surname,firstname

FROM employees

WHERE employee_id=in_sales_rep_id;

SELECT customer_id,customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

END;

To process this stored procedure, we first call multi_query( ) to set up the multiple results, and then we call store_result( ) to initialize each result set. We can use fetch_object( ) or fetch_row( ) to access each row in the result set. Example 13-18 illustrates this technique.

Example 13-18. Fetching two result sets from a stored procedure in mysqli

$query = "call stored_proc_with_2_results( $employee_id )";

if ($mysqli->multi_query($query)) {

$result = $mysqli->store_result( );

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

printf("%d %s %s\n",$row->employee_id,$row->surname,$row->firstname);

}

$mysqli->next_result( );

$result = $mysqli->store_result( );

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

printf("%d %s \n",$row->customer_id,$row->customer_name);

}

}

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. The next_ result( ) method will return TRUE if there is an additional result set, and we can use the field_count property andfetch_field( ) method to retrieve the number of columns as well as their names and other properties, as shown in Example 13-19.

Example 13-19. mysqli code to process a variable number of result sets

1 $query = "call stored_proc_with_2_results( $employee_id )";

2 if ($mysqli->multi_query($query)) {

3 do {

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

5 while ($finfo = $result->fetch_field( )) {

6 printf("%s\t", $finfo->name);

7 }

8 printf("\n");

9

10 while ($row = $result->fetch_row( )) {

11 for ($i=0;$i<$result->field_count;$i++) {

12 printf("%s\t", $row[$i]);

13 }

14 printf("\n");

15 }

16 $result->close( );

17 }

18 } while ($mysqli->next_result( ));

Let's look at this example line by line:

Using MySQL with PHP Data Objects

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.

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

<?php

$dsn = 'mysql:dbname=prod;host=localhost;port=3305';

$user = 'root';

$password = 'secret';

try {

$dbh = new PDO($dsn, $user, $password);

}

catch (PDOException $e) {

die('Connection failed: '.$e->getMessage( ));

}

print "Connected\n";

?>

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

$sql="CREATE TABLE my_numbers (a_number INT)";

$dbh->exec($sql);

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

$rows=$dbh->exec("INSERT INTO my_numbers VALUES (1), (2), (3)");

printf("%d rows inserted\n",$rows);

Catching Errors

Some PDO methods return a success or failure status, while others—like $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, and—if the SQLSTATE does not indicate success (00000)—prints the error information from errorInfo( ).

Example 13-23. Using PDO error status methods

$sql="CREATE TABLE my_numbers (a_number INT)";

$dbh->exec($sql);

if ($dbh->errorCode( )<>'00000') {

$error_array=$dbh->errorInfo( );

printf("SQLSTATE : %s\n",$error_array[0]);

printf("MySQL error code : %s\n",$error_array[1]);

printf("Message : %s\n",$error_array[2]);

}

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

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

SQLSTATE : 42S01

MySQL error code : 1050

Message : Table 'my_numbers' already exists

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

$sql="CREATE TABLE my_numbers (a_number INT)";

$dbh->exec($sql);

if ($dbh->errorCode( )<>'00000') {

die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");

}

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

$dbh->beginTransaction( );

$dbh->exec("UPDATE account_balance

SET balance=balance-$tfer_amount

WHERE account_id=$from_account");

if ($dbh->errorCode( )<>'00000') {

printf("transaction aborted: %s\n",implode(': ',$dbh->errorInfo( )));

$dbh->rollback( );

}

else

{

$dbh->exec("UPDATE account_balance

SET balance=balance+$tfer_amount

WHERE account_id=$to_account");

if ($dbh->errorCode( )<>'00000')

{

printf("transaction aborted: %s\n",implode(': ',$dbh->errorInfo( )));

$dbh->rollback( );

}

else

{

printf("transaction succeeded\n");

$dbh->commit( );

}

}

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

$sql = 'SELECT department_id,department_name FROM departments';

foreach ($dbh->query($sql) as $row) {

printf("%d \t %s\n",$row['department_id'],$row['department_name']);

}

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

Example 13-28. Accessing query results by column number

$sql = 'SELECT department_id,department_name FROM departments';

foreach ($dbh->query($sql) as $row) {

printf("%d \t %s\n",$row[0],$row[1]);

}

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.

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

$sql = 'INSERT INTO my_numbers VALUES(1),(2),(3)';

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

$sth->execute() or die (implode(':',$sth->errorInfo( )));

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

$sql='SELECT department_id,department_name FROM departments LIMIT 5';

$sth=$dbh->prepare($sql) or die (implode(':',$sth->errorInfo( )));

$sth->execute() or die (implode(':',$sth->errorInfo( )));

while($row=$sth->fetch( )) {

printf("%d \t %s \n",$row['department_id'],$row['department_name']);

}

Binding Parameters to a Prepared Statement

We usually create prepared statements with the intention of re-executing the statement—often 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) and—for certain data types such as strings—a 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

1 $sql='SELECT customer_id,customer_name

2 FROM customers

3 WHERE sales_rep_id=:sales_rep_id

4 AND contact_surname=:surname';

5 $sth = $dbh->prepare($sql);

6 if ($dbh->errorCode( )<>'00000') {

7 die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");

8 }

9

10 $sth->bindParam(':sales_rep_id', $sales_rep_id, PDO::PARAM_INT);

11 $sth->bindParam(':surname', $surname, PDO::PARAM_STR, 30);

12

13 $sales_rep_id=41;

14 $surname = 'SMITH';

15 $sth->execute( );

16 if ($dbh->errorCode( )<>'00000') {

17 die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");

18 }

19 while($row=$sth->fetch( )) {

20 printf("%d %s \n",$row['customer_id'],$row['customer_name']);

21 }

Let's look at this example line by line:

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( ).

Table 13-1. Elements of the getColumnMeta( ) array

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

$sth = $dbh->prepare("SELECT employee_id,surname,date_of_birth

FROM employees where employee_id=1");

$sth->execute() or die (implode(':',$sth->errorInfo( )));

$cols=$sth->columnCount( );

for ($i=0; $i<$cols ;$i++) {

$metadata=$sth->getColumnMeta($i);

printf("\nDetails for column %d\n",$i+1);

printf(" Name: %s\n",$metadata["name"]);

printf(" Datatype: %s\n",$metadata["native_type"]);

printf(" Length: %d\n",$metadata["len"]);

printf(" Precision: %d\n",$metadata["precision"]);

}

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

1 function sql_to_html($dbh,$sql_text) {

2 require_once "HTML/Table.php";

3 $table = new HTML_Table('border=1');

4

5 $sth = $dbh->prepare($sql_text) or die(implode(':', $sth->errorInfo( )));

6 $sth->execute() or die(implode(':', $sth->errorInfo( )));

7 $cols = $sth->columnCount( );

8

9 for ($i = 0; $i < $cols; $i ++) {

10 $metadata = $sth->getColumnMeta($i);

11 $table->setCellContents(0, $i, $metadata["name"]);

12 }

13 $table->setRowAttributes(0, array ("bgcolor" => "silver"));

14

15 $r = 0;

16 while ($row = $sth->fetch( )) {

17 $r ++;

18 for ($i = 0; $i < $cols; $i ++) {

19 $table->setCellContents($r, $i, $row[$i]);

20 }

21 }

22

23 print $table->toHtml( );

24 }

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.

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

$sql='call simple_stored_proc( )';

$dbh->exec($sql);

if ($dbh->errorCode( )<>'00000') {

die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");

}

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

$sql = 'call stored_proc_with_1_result( )';

foreach ($dbh->query($sql) as $row) {

printf("%d \t %s\n",$row[0],$row[1]);

}

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( )

$sql='call stored_proc_with_1_result( )';

$sth=$dbh->prepare($sql) or die (implode(':',$sth->errorInfo( )));

$sth->execute() or die (implode(':',$sth->errorInfo( )));

while($row=$sth->fetch( )) {

printf("%s \t %s \n",$row['department_name'],$row['location']);

}

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

$sql='CALL customers_for_rep(:sales_rep_id,:surname)';

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

if ($dbh->errorCode( )<>'00000') {

die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");

}

$sth->bindParam(':sales_rep_id', $sales_rep_id, PDO::PARAM_INT);

$sth->bindParam(':surname', $surname, PDO::PARAM_STR, 30);

$sales_rep_id=41;

$surname = 'SMITH';

$sth->execute( );

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

CREATE PROCEDURE stored_proc_with_2_results(in_sales_rep_id INT)

DETERMINISTIC READS SQL DATA

BEGIN

SELECT employee_id,surname,firstname

FROM employees

WHERE employee_id=in_sales_rep_id;

SELECT customer_id,customer_name

FROM customers

WHERE sales_rep_id=in_sales_rep_id;

END;

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

$sth = $dbh->prepare("call stored_proc_with_2_results( $employee_id )");

$sth->execute() or die (implode(':',$sth->errorInfo( )));

while ($row1=$sth->fetch( )) {

printf("%d %s %s\n",$row1['employee_id'],$row1['surname'],$row1['firstname']);

}

$sth->nextRowset( );

while ($row2=$sth->fetch( )) {

printf("%d %s \n",$row2['customer_id'],$row2['customer_name']);

}

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

CREATE PROCEDURE sp_employee_report(in_emp_id decimal(8,0))

READS SQL DATA

BEGIN

DECLARE customer_count INT;

SELECT surname,firstname,date_of_birth

FROM employees

WHERE employee_id=in_emp_id;

SELECT department_id,department_name

FROM departments

WHERE department_id=

(select department_id

FROM employees

WHERE employee_id=in_emp_id);

SELECT count(*)

INTO customer_count

FROM customers

WHERE sales_rep_id=in_emp_id;

IF customer_count=0 THEN

SELECT 'Employee is not a current sales rep';

ELSE

SELECT customer_name,customer_status

FROM customers

WHERE sales_rep_id=in_emp_id;

SELECT customer_name,sum(sale_value)

FROM sales JOIN customers USING (customer_id)

WHERE customers.sales_rep_id=in_emp_id

GROUP BY customer_name;

END IF;

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

1 function many_results($dbh, $sql_text) {

2 $sth = $dbh->prepare($sql_text);

3 $sth->execute() or die(implode(':', $sth->errorInfo( )));

4

5 do {

6 if ($sth->columnCount( ) > 0) { /* Yes, there is a result set */

7

8 #Print off the column names

9 for ($i = 0; $i < $sth->columnCount( ); $i ++) {

10 $meta = $sth->getColumnMeta($i);

11 printf("%s\t", $meta["name"]);

12 }

13 printf("\n");

14

15 #Loop through the rows

16 while ($row = $sth->fetch( )) {

17 #Loop through the columns

18 for ($i = 0; $i < $sth->columnCount( ); $i ++) {

19 printf("%s\t", $row[$i]);

20 }

21 printf("\n");

22

23 }

24 printf("-------------------\n");

25 }

26 }

27 while ($sth->nextRowset( ));

28 }

Let's walk through this example:

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

CREATE PROCEDURE 'sp_rep_customer_count'(

in_emp_id DECIMAL(8,0),

OUT out_cust_count INT)

READS SQL DATA

BEGIN

SELECT count(*) AS cust_count

INTO out_cust_count

FROM customers

WHERE sales_rep_id=in_emp_id;

END ;

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)

sql = "call sp_rep_customer_count(?,?)";

$sth = $dbh->prepare($sql) or die(implode(':', $sth->errorInfo( )));

$sth->bindParam(1,$sales_rep_id,PDO::PARAM_STR,4000);

$sth->bindParam(2,$customer_count, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);

$sth->execute() or die(implode(':', $sth->errorInfo( )));

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

$sql="call sp_rep_customer_count(1,@customer_count)";

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

$sth->execute() or die (implode(':',$sth->errorInfo( )));

# Now get the output variable

$sql="SELECT @customer_count";

foreach ($dbh->query($sql) as $row) {

printf("Customer count=%d\n",$row[0]);

}

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.

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

CREATE PROCEDURE sp_mysql_info(in_database VARCHAR(60),

OUT server_version VARCHAR(100))

READS SQL DATA

BEGIN

DECLARE db_count INT;

SELECT @@version

INTO server_version;

SELECT 'Current processes active in server' AS table_header;

SHOW FULL PROCESSLIST;

SELECT 'Databases in server' AS table_header;

SHOW DATABASES;

SELECT 'Configuration variables set in server' AS table_header;

SHOW GLOBAL VARIABLES;

SELECT 'Status variables in server' AS table_header;

SHOW GLOBAL STATUS;

SELECT COUNT(*)

INTO db_count

FROM information_schema.schemata s

WHERE schema_name=in_database;

IF (db_count=1) THEN

SELECT CONCAT('Tables in database ',in_database) AS table_header;

SELECT table_name

FROM information_schema.tables

WHERE table_schema=in_database;

END IF;

END;

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

1 <HTML>

2 <TITLE>MySQL Server Statistics</TITLE>

3 <H1>Enter MySQL Server Details</H1>

4 Enter your database connection details below:

5 <p>

6 <FORM ACTION="<?php echo $_SERVER['PHP_SELF']; ?>" METHOD=POST>

7 <TABLE>

8 <TR><TD>Host:</TD><TD> <input type="text" name="mhost"></TD></TR>

9 <TR><TD>Port:</TD><TD> <input type="text" name="mport"></TD></TR>

10 <TR><TD>Username:</TD><TD> <input type="text" name="muser"></TD></TR>

11 <TR><TD>Password:</TD><TD> <input type="password" name="mpass"></TD></TR>

12 <TR><TD>Database:</TD><TD> <input type="test" name="mdb"></TD></TR>

13 </TABLE>

14 <TR><TD><input type="submit" name="Submit" value="Submit">

15 </FORM>

16

17 <?php

18 require_once "HTML/Table.php";

19

20 $html_text = array ( );

21

22 if (IsSet ($_POST['Submit'])) {

23 $dsn = 'mysql:dbname='.$_POST['mdb'].';host='.$_POST['mhost'].

24 ';port='.$_POST['mport'];

25 $user = $_POST['muser'];

26 $password = $_POST['mpass'];

27

28 try {

29 $dbh = new PDO($dsn, $user, $password);

30 } catch (PDOException $e) {

31 echo 'Connection failed: '.$e->getMessage( );

32 }

33 $sql = 'call sp_mysql_info(:dbname,@server_version)';

34 $sth = $dbh->prepare($sql);

35 $sth->bindParam(':dbname', $_POST['mdb'], PDO::PARAM_STR, 30);

36 $sth->execute() or die(implode(':', $sth->errorInfo( )));

37

38 do {

39 if ($sth->columnCount( ) > 0) { /* Yes, there is a result set */

40 $col0 = $sth->getColumnMeta(0);

41 if ($col0["name"] == "table_header") { /*format this as a heading */

42 $row = $sth->fetch( );

43 array_push($html_text, "<h2>$row[0]</h2>");

44 }

45 else { /* Format this as a table */

46 $table = new HTML_Table('border=1');

47 for ($i = 0; $i < $sth->columnCount( ); $i ++) {

48 $meta = $sth->getColumnMeta($i);

49 $table->setCellContents(0, $i, $meta["name"]);

50 }

51 $table->setRowAttributes(0, array ("bgcolor" => "silver"));

52

53 #Loop through the rows

54 $r = 0;

55 while ($row = $sth->fetch( )) {

56 #Loop through the columns in the row

57 $r ++;

58 for ($i = 0; $i < $sth->columnCount( ); $i ++) {

59 $table->setCellContents($r, $i, $row[$i]);

60 }

61 }

62 array_push($html_text, $table->toHtml( ));

63 }

64 }

65 }

66 while ($sth->nextRowset( ));

67

68 foreach ($dbh->query("SELECT @server_version") as $row) {

69 $mysql_version = $row[0];

70 }

71

72 print "<h1>MySQL Server status and statistics</h1>";

73 printf("<b>Host:</b> %s<br>", $_POST['mhost']);

74 printf("<b>Port:</b> %s<br>", $_POST['mport']);

75 printf("<b>Version:</b> %s<br>", $mysql_version);

76 foreach($html_text as $html) {

77 print $html;

78 }

79 }

80 ?>

81 </html>

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.

Conclusion

In this chapter we saw how we can use MySQL stored programs within PHP by using either the mysqli or PDO extension. Both interfaces provide all the tools you need to take advantage of MySQL stored procedures and functions from within your PHP application.

Figure 13-3. Output from our complete PDO example