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

getMessage( ));
}

print "Connected
";

?>

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
",$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
",$error_array[0]);
 printf("MySQL error code : %s
",$error_array[1]);
 printf("Message : %s
",$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( ))."
");
}

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
",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
",implode(': ',$dbh->errorInfo( )));
 $dbh->rollback( );
 }
 else
 {
 printf("transaction succeeded
");
 $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 	 %s
",$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 	 %s
",$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 	 %s 
",$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( ))."
");
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( ))."
");
18 }
19 while($row=$sth->fetch( )) {
20 printf("%d %s 
",$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("
Details for column %d
",$i+1);
 printf(" Name: %s
",$metadata["name"]);
 printf(" Datatype: %s
",$metadata["native_type"]);
 printf(" Length: %d
",$metadata["len"]);
 printf(" Precision: %d
",$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( ))."
");
}

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 	 %s
",$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 	 %s 
",$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( ))."
");
}

$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
",$row1['employee_id'],$row1['surname'],$row1['firstname']);
}

$sth->nextRowset( );

while ($row2=$sth->fetch( )) {
 printf("%d %s 
",$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	", $meta["name"]);
12 }
13 printf("
");
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	", $row[$i]);
20 }
21 printf("
");
22
23 }
24 printf("-------------------
");
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
",$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

2MySQL Server Statistics

1 
3

Enter MySQL Server Details

4 Enter your database connection details below: 5

6

7 8 9 10 11 12 13
Host:  
Port:  
Username:  
Password:  
Database:  
14
15 16 17 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, "

$row[0]

"); 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 "

MySQL Server status and statistics

"; 73 printf("Host: %s
", $_POST['mhost']); 74 printf("Port: %s
", $_POST['mport']); 75 printf("Version: %s
", $mysql_version); 76 foreach($html_text as $html) { 77 print $html; 78 } 79 } 80 ?> 81

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.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net