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 with mysqli, you might want to skip forward to "Calling Stored Programs with mysqli," later in this chapter.

13.2.1. 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

 

13.2.2. 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$mysqlirepresenting a MySQL connection, and check for any error condition.

Example 13-1. Creating a mysqli connection

# Create a connection

13.2.3. 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
"
 ,$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
"
 ,$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
"
 ,$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.

13.2.4. 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
"
 ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
}

13.2.5. 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	%s	%d
",$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	%s	%d
",$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).

13.2.6. Managing Transactions

As with most of the programmatic interfaces to MySQL, you are always free to manage transactions by executing the usual MySQL statementsfor 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
",$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
",$mysqli->error);
 $mysqli->rollback( );
 }
 else {
 printf("transaction succeeded
");
 $mysqli->commit( );
 }
}

13.2.7. 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 execute calls 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:

Line	Explanation
2	Prepare an INSERT statement. The statement has two input parameters, corresponding to the values to be inserted into the table.
5	Use bind_param( ) to associate PHP variables with the SQL parameters. bind_param( ) takes two input values: first a string indicating the data types of the parameters to follow (i=integer, d=double, s=string, b=blob). So the "is" string indicates that the first parameter is to be treated as an integer, and the second as a string. The following arguments to bind_param( ) signify the PHP variables to be associated with the ? placeholders.
7	Create a loop that repeats for each of the numbers 1 to 10.
9	Execute the prepared statement. Each execution will insert the values of the PHP variables $my_number and $my_string into the table.
11	Close the prepared statement, releasing any resources associated with the statement.

 

13.2.8. 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
", $employee_id,$surname,$firstname);
 }

13.2.9. 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
", $colinfo->name);
 printf("max. Len: %d
", $colinfo->max_length);
 printf("Type: %d

", $colinfo->type);
 }

13.2.10. 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:

Line(s)	Explanation
1 and 2	Set up the HTML table that will hold our result set. We're using the PEAR Table class to create our HTML tableavailable at http://pear.php.net.
4	Prepare the SQL statement. The text of the SQL statement is contained in the variable $sql: we don't have to know the text of the SQL, since this code will process the output from any SELECT statement.
8	Retrieve the result set metadata.
9	Note the number of columns that will be returned by the query.
10-13	Retrieve the name of each column to be returned into an array.
14 and 15	Create and format a nHTML table row containing our column names.
19	Initialize an array that will contain the column values for each row returned by the SQL statemnet.
21	Create an array variable that we are going to use to pass to the bind_result( ) call. To perform a dynamic bind ,we have to use the procedural version of bind_result( )mysqli_stmt_bind_result( )which takes as its first argument the prepared statement object. So the first element of our array is the statement object.
23 and 24	Add an element to $bind_result_parms for each column to be returned. Because mysqli_stmt_bind_result( ) expects to have these passed "by reference" rather than "by value," we prefix these array elements with the & symbol.
27	Bind the result variables to the dynamic SQL. The process is complicatedbecause bind_result( ) cannot accept an array of result variables, we need to call the PHP function call_user_func_array( ), which allows an array to be passed as an argument to a function that normally requires a static set of variables. We also have to use the procedural version of bind_result( ), mysqli_stmt_bind_result( ). Neverthelessdespite the complexitywe have now successfully bound the elements of stmt_results to receive the output of the fetch command.
2834	Execute the SQL and fetch the results of the SQL. The results for each column will be placed in the stmt_results array.
36 and 37	Close the prepared statement and print out the contents of the HTML table that we have built.

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 outputand 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.

13.2.11. 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
");
 }

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	%s
", $row->department_name, $row->location);
 }

You will often want to execute the same stored procedure multiple timespossibly with varying input parametersso 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 
", $customer_id,$customer_name);
13 }

Let's look at this example line by line:

Line(s)	Explanation
13	Create a prepared statement for the stored procedure call; the ? symbol in the SQL text indicates the presence of an input parameter.
5	Associate a PHP variable ($in_sales_rep_id) with the stored procedure's input parameter.
710	Execute the stored procedure and associate PHP variables ($customer_id and $customer_name) with the columns in the output result set.
1113	Retrieve the result set from the stored procedure call.

 

13.2.12. 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
",$row->customer_count);

13.2.13. 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
",$row->employee_id,$row->surname,$row->firstname);
 }
 $mysqli->next_result( );
 $result = $mysqli->store_result( );
 while ($row = $result->fetch_object( )) {
 printf("%d %s 
",$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 and fetch_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	", $finfo->name);
7 }
8 printf("
");
9
10 while ($row = $result->fetch_row( )) {
11 for ($i=0;$i<$result->field_count;$i++) {
12 printf("%s	", $row[$i]);
13 }
14 printf("
");
15 }
16 $result->close( );
17 }
18 } while ($mysqli->next_result( ));

Let's look at this example line by line:

Line(s)	Explanation
2	Use the multi_query( ) call to invoke the stored procedure.
318	Define a loop that will continue so long as mysqli->next_result( ) returns TRUE: the loop will execute at least once, and then will continue as long as there are result sets to process.
4	Use store_result( ) to retrieve the result set into the $result object. We can use either store_result( ) or use_result( ): store_result( ) uses more memory, but allows some additional functionality (such as seek_result( )).
57	Loop through the column in the result set. Each call to fetch_field( ) stores the details of a new column into the $finfo object. On line 6 we print the name of the column.
1015	This loop repeats for each row in the result set. We use fetch_row( ) rather than fetch_object( ), since it is easier to refer to a column by number when we do not know its name.
1113	Loop through each column in a particular row. We use the field_count property of the result set to control that loop. On line 12 we print the value of a particular column, referring to the column by number.
16	Close the result set.
18	The while condition on this line will cause the loop to repeat if there is an additional result set and to terminate otherwise.