Section 13.3. Using MySQL with PHP Data Objects

13.3. 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. 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 <?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"; ?> 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 $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); 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 $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"); } 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 $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( ); } } 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 $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. 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 $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']); } 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 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: Line(s) Explanation 15 Prepare a PDO statement for a SELECT statement that will retrieve customer details for a particular customer contact_surname and sales_rep_id. Placeholders are defined in the SQL text to represent the values for those two columns. 10 Call the bindParam( ) method to associate the PHP variable $sales_rep_id with the placeholder :sales_rep_id. The third parameter indicates the data type of the placeholder. A complete list of PDO data types can be found in the PDO documentation (see http://www.php.net/manual/en/ref.pdo.php). 11 Call bindParam( ) again to associate a PHP variable with the :surname placeholder. In this case, we also specify a maximum length for the parameter as specified in the fourth parameter. 1314 Assign values to the PHP variables that have been associated with the prepared statement placeholders. Typically, we would assign new values to these variables before we execute the prepared statement. 1522 Execute the prepared statement and retrieve rows in the usual fashion. 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( ). Table 13-1. Elements of the getColumnMeta( ) array Array element name Description native_type MySQL data type of the column flags Any special flags, for the column, such as "not null" name Display name for the column len Length of the column precision Precision for decimal or floating-point numbers pdo_type Internal PDO data type used to store the value 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"]); } 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 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: Line(s) Explanation 2 and 3 Initialize the HTML table. We're using the PEAR Table class to create our HTML table (available at http://pear.php.net). 5 and 6 Prepare and execute the SQL in the usual fashion. 7 Retrieve the number of columns in the result set. We'll need to refer to the column count several times, so it's handy to store the results in a local variable. 912 Loop through the columns. For each column, we retrieve the column name and add that column name to the header row in our HTML table. 1621 Loop through the rows from the result set using the fetch( ) method in the usual fashion. 1820 Loop through the columns returned for a particular row. On line 19 we apply the column value to the appropriate cell of the HTML table. 23 Print the HTML to generate the table. 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 $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']); } 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 $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( ); 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 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: Line(s) Explanation 23 Prepare and execute a stored procedure call in the usual manner. 527 This is our main loop. It executes once for each result set returned by the stored procedure it will continue until nexTRowset( ) returns FALSE. Note that this loop will always execute at least once (though it may do nothing if there are no rows returned). 6 Check to make sure that there is a result set. Remember that the loop will execute at least once, so we should check that there is at least one result set. 912 Loop through the column names and print them off (as a header row). 1623 This loop repeats once for each row returned by a result set. 1820 Loop through each column in the current row and print out its value. 27 Having processed all columns in all the rows for a particular result set, we call nextrowset( ) to move onto the next result. If nexTRowset( ) returns FALSE, then we will terminate the loop having processed all of the output. 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 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. 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 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: Line(s) Explanation 115 Create the HTML form in which the user enters the server details. This is standard PHP HTML. You can see the resulting input form in Figure 13-3. 18 We are using the PEAR HTML Table module to create our HTML tables. You can obtain this from http://pear.php.net. 20 Create an array to store our HTML. We do this because we want to display the MySQL version string before the HTML tables, although as a stored procedure output variable we can only retrieve it after all result sets have been closed. So we need to store our HTML in a variable rather than print it as we go. 22 This if statement starts the section of code that is executed once the user clicks the Submit button defined on line 14. 2332 Build up the PDO dsn string from the user input and connect to the MySQL server. 3336 Prepare and execute the stored procedure, binding as an input parameter the database name provided in the HTML form. A user variable@server_version is provided to receive the value of the second, output parameter. 3866 This is the loop that will repeat for each result set returned by the stored procedure. The loop will continue as long as the $sth->nextRowset( ) call on line 66 returns true. 4246 If the first column in the result set is named table_header, then this result set is a "title" for the subsequent result set, so we format the column value as an HTML header (line 45). 4748 Otherwise (the result set is not a "title"), create a new table object to contain the result set output. 4751 Retrieve the column names for the result set and add them to the first row of the HTML table. 5461 Loop through each row of the output and push the column values into the appropriate cells of the HTML table. 62 Add the HTML for the table to our array variablewe'll print the contents of this array later (after we get the value of the output parameter). 6870 Now that all result sets have been retrieved, we can get the value of the output parameter, which is now contained in the user variable @server_version. 7275 Print the major header line, and some server details, including host, port, and MySQL server version. 7678 Print all of the HTML that we saved in the $html_text variable. This includes the HTML tables and headings. Figure 13-3 shows the output from this PHP example.