Demonstration of Stored Procedures using MySQLi

The aim of this exercise is to demonstrate various ways to query a simple members database using.

• Simple MySQLi commands and
• Stored Procedures with Single and Mutliple Result sets AND
• To make sure it operates correctly on the Webhosting Server!

NOTE: The include for the Database connection has been left out of the samples.

SQL used to create the Tables and Stored Procedures

The sample Members Database and Stored Procedure SQL commands are....

The SQL Code

####################################################################
#
# This file contains the SQL for the sample tables and stored procedures as
# used in the sample_x.php files supplied.
#
####################################################################
DROP TABLE IF EXISTS `test_member` ;

CREATE TABLE IF NOT EXISTS  `test_member` (
 `id` int(11)  NOT NULL AUTO_INCREMENT,
`username` varchar(16) DEFAULT '' NOT NULL,
`password` varchar(48) DEFAULT '' NOT NULL,
`firstname` varchar(64) DEFAULT '' NOT NULL,
`lastname` varchar(64) DEFAULT '' NOT NULL,
`email` varchar(128) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
);
-- Insert some test Data into the DB
INSERT INTO test_member (username,password,firstname,lastname,email) values
('goliath','aa','Fred','Smith','fredsmith@lookingovermyshoulder.com'),
('rasputin','bb','George','Smith','georgesmith@lookingovermyshoulder.com'),
('rascal','cc','Sam','Smith','samsmith@lookingovermyshoulder.com'),
('bertha','dd','Bertha','Smith','berthasmith@lookingovermyshoulder.com'),
('jollygiant','ee','Bert','Smith','bertsmith@lookingovermyshoulder.com');

-- Returns One Result Set
-- 1. The status via result and message
--
DROP PROCEDURE IF EXISTS `test_member_check_username` ;
CREATE PROCEDURE `test_member_check_username`(
  m_username varchar(16) )
BEGIN
  DECLARE result int(1) DEFAULT 0;
  DECLARE message varchar(128) DEFAULT '';
  DECLARE user_id int(11);

  SELECT r.id INTO user_id
    FROM test_member r
      WHERE r.username = m_username;
  IF user_id IS NOT NULL THEN
    BEGIN
      SET result = 1;
      SET message = CONCAT('User name ',m_username,' already exists');
    END;
  END IF;
SELECT result,message;
END;

-- Returns Two Results Sets
-- 1. The status via result and message and
-- 2. The memers information if the user_id is found.
--
DROP PROCEDURE IF EXISTS `test_member_get_info`;
CREATE PROCEDURE `test_member_get_info`(
  m_user_id int(11)
)
BEGIN
  DECLARE result int(1) DEFAULT 0;
  DECLARE message varchar(128) DEFAULT '';
  DECLARE user_id int(11);

  SELECT r.id INTO user_id
    FROM test_member r
      WHERE r.id = m_user_id
	LIMIT 1;
  IF user_id IS NULL THEN
    BEGIN
      SET result = 1;
      SET message = CONCAT("User id ", m_user_id, " wasn't found");
    END;
  END IF;
SELECT result,message;
  IF user_id IS NOT NULL THEN
  BEGIN
    SELECT id,username,password,firstname,lastname,email
      FROM test_member
	WHERE id= user_id;
  END;
  END IF;
END;

Example 1

Use a direct SQL Call from PHP to check for an existing username. We are expecting to see an error for a duplicate user

This is checking for a matching username and will return the matching id. We are expecting there to be No Matches and therefor the mysqli_num_rows($result) should be 0.

The PHP Code

<?php
##########################################################################################
// 
// Filename: sample_1.php
// Author: Tim Brownlaw
// Description: Perform a direct MySQLi Call and display the result
//
##########################################################################################
  $message ='';		// Initialise the string so we can Concatenate later using ".="
  $username = 'jollygiant'; // Choose a known username that should give us a match

  // Create the query
  $query = "SELECT id FROM test_member WHERE username = '$username'";
  $result = mysqli_query($db_connect,$query) or die(mysqli_error($db_connect));

// On Success $result will be a Mysqli Object
// On Failure $result = FALSE
// For successful queries that do not return a Mysqli Object  $result = TRUE

// In this case we are expecting a Mysqli Object Or FALSE

  if($result)
  {
    if(mysqli_num_rows($result) > 0)
    {
      $message .= 'Whoops - It appears that someone is using the username - ';
      $message .= $username;
    }
    else
    {
      $message .= 'It appears that the username - ';
      $message .= $username;
      $message .= ' -  is not being used';
    }
  }
  else
  {
// There is an error with the database so let's shout out about it
    $message .= 'There was an error with the database';
    $message .= mysqli_errno($db_connect);
  }
  mysqli_free_result($result);  // Let's clean up before moving on
// Display what happened.
  echo $message;
echo "<br /> Hi everyone";
?>

The Result...

Whoops - It appears that someone is using the username - jollygiant
Hi everyone

Example 2

Now we will call a Stored Procedure to perform the same function as above
We are expecting an error saying the user already exists

The PHP Code

<?php
##########################################################################################
//
// Filename: sample_2.php
// Author: Tim Brownlaw
// Description: Use a Stored Procedure call to check for the existance of a username.
// The result is
//   $row['result']  = 0 on No Match
// 		$row['message'] = ''
//   $row['result']  = 1 on a Match
// 		$row['message'] - The Error message is being generated in the Stored Procedure.
//                                           
##########################################################################################
  $message ='';		// Initialise the string so we can Concatenate later using ".="
  $username = 'jollygiant'; // Choose a known username that should give us a match

  $query = "CALL test_member_check_username('$username')";
  $result = mysqli_query($db_connect,$query) or die(mysqli_error($db_connect));
  if($result)
  {
    if(mysqli_num_rows($result)>0)
      $row = mysqli_fetch_assoc($result);
      if($row['result'] > 0)
	$message .= $row['message'];
      else
	$message .= 'This username is available';
  }
  else
    $message .= 'Database Error';

// The next two lines are required to allow further Stored Procedure Calls
//  else we'll get that famous - "Out of Sync" Error Message.
  mysqli_next_result($db_connect);
  mysqli_free_result($result);

// Display what happened
  echo $message; 

The Result...

User name jollygiant already exists

Example 3

Now we will call a Stored Procedure that returns Two result sets that we need to deal with
In this case we'll attempt to find a user with a user_id of 66 which doesn't exist.
So we should see an error.

The PHP Code

<?php
##########################################################################################
//
// Filename: sample_3.php
// Author: Tim Brownlaw
// Description:  Retrieve 2 Datasets from a Stored Procedure
//   1. is the result and message and
//   2. is the user information if a valid id exists.
//
// The result is
// No Error:
//   $row['result']  = 0  , $row['message'] = '' and
//   $user_row[''] =  the users information
// On Error:
//   $row['result']  = 1 and $row['message'] will be the error message
//   $user_row[''] won't be created.
//
##########################################################################################
  $message ='';		// Initialise the string so we can Concatenate later using ".="
  $user_id = 66; // this is id that is known Not to exist in the Database

  $query = "CALL test_member_get_info(".$user_id.")";
  $result = mysqli_query($db_connect,$query) or die(mysqli_error($db_connect));

  if($result)
  {
    if(mysqli_num_rows($result)>0)
      $row = mysqli_fetch_assoc($result);
      if($row['result'] > 0)
      {
	$message .= $row['message'];
      }
      else
      {
	mysqli_next_result($db_connect); // prepare the next result
	$result = mysqli_use_result($db_connect);  // Get the next result
	$user_row = mysqli_fetch_assoc($result);
	$message .= "Now ";
	$message .= $user_row['username'];
	$message .= "'s First name is ";
	$message .= $user_row['firstname'];
	$message .= " and lastname is ";
	$message .= $user_row['lastname'];
	$message .= ".<br /> Email address is ";
	$message .= $user_row['email'];
      }
  }
  else
	$message .= 'Database Error';

  // The next two lines are required to allow further Stored Procedure Calls
  //  else we'll get that famous - "Out of Sync" Error Message.
    mysqli_next_result($db_connect);
    mysqli_free_result($result);

  // Display what happened
    echo $message; 

The Result...

User id 66 wasn't found

Example 4

Now this example is going to read back two result sets from the SP.
The First is the result and message ( result = 0 and message is blank for a success)
Then the next result set is read which contains the members information.

The PHP Code

<?php
##########################################################################################
//
// Filename: sample_4.php
// Author: Tim Brownlaw
// Description:  Retrieve 2 Datasets from a Stored Procedure
//   1. is the result and message and
//   2. is the user information if a valid id exists.
//
// The result is
// No Error:
//   $row['result']  = 0  , $row['message'] = '' and
//   $user_row[''] =  the users information
// On Error:
//   $row['result']  = 1 and $row['message'] will be the error message
//   $user_row[''] won't be created.
//
##########################################################################################

  $message ='';		// Initialise the string so we can Concatenate later using ".="
  $user_id = 1; // this is an id that is known to exist in the Database

  $query = "CALL test_member_get_info(".$user_id.")";
  $result = mysqli_query($db_connect,$query) or die(mysqli_error($db_connect));

  if($result)
  {
    if(mysqli_num_rows($result)>0)
      $row = mysqli_fetch_assoc($result);
      if($row['result'] > 0)
      {
	$message .= $row['message'];
      }
      else
      {
	mysqli_next_result($db_connect); // prepare the next result
	$result = mysqli_use_result($db_connect);  // Get the next result
	$user_row = mysqli_fetch_assoc($result);
	$message .= "Now ";
	$message .= $user_row['username'];
	$message .= "'s First name is ";
	$message .= $user_row['firstname'];
	$message .= " and lastname is ";
	$message .= $user_row['lastname'];
	$message .= ".<br /> Email address is ";
	$message .= $user_row['email'];
      }
  }
  else
	$message .= 'Database Error';

  // The next two lines are required to allow further Stored Procedure Calls
  //  else we'll get that famous - "Out of Sync" Error Message.
    mysqli_next_result($db_connect);
    mysqli_free_result($result);

  // Display what happened
    echo $message; 

The Result...

Now goliath's First name is Fred and lastname is Smith.
Email address is fredsmith@lookingovermyshoulder.com

CodeIgniter Snippets

Member Model Function

This is the function that is called by the controller.

The PHP Code

<?php
  function member_get_profile($id)
  {
    $error = FALSE;
    $next_row = array();
    $sql = 'CALL member_get_profile(?)';
    $params =array($id);
    $result = $this->db->query($sql,$params);
      if(($result) && ($result->num_rows() > 0))
	$row = $result->row_array(); // Get the First Result Set back
      else
      {
	$row =array(
		'result'=>1,
		'message'=>'Something went wrong with the query'
		 );
	$error = TRUE;
      }
    if ($error == FALSE)
    {
// Is there a next result?
      if($result->use_result())
      {
	$next_row = $result->row_array();
	$result->free_result();
      }
    }
// Always perform these statements
    $result->next_result();
    $result->free_result();
// This is to cater for the strange case where the 2nd result is not being returned
    if(count($next_row) ==0)
      $row =array('result'=>2,
	'message'=>"We didn't get the second result returned from 'member_get_profile' <br />
	This is a system issue on the host server we are currently looking into. <br />
	It worked fine on my development system!"
	);
      $row = array('result'=>$row, 'data'=>$next_row);
    return $row;
  }

Changes to database/mysqli/mysqli_result.php

These changes made to the mysqli_result.php work a treat on my local server. I had put in echo statements to show where the code gets upto when called both under the Local System and hosted system and it appears that the mysql_next_result() is coming back FALSE on the hosted system.

That is the strange part. The same command in the above samples seem to work just fine.... Unless I am missing something which is highly likely after having stared at this for far too long.

The PHP Code

<?php
   /**
	* Prepare next result
	*
	* @return    bool
	*/
	function next_result()
	{
	   return mysqli_next_result($this->conn_id);
	}
    /**
	* Prepare next result
	*
	* @return    mysqli object
	*/
// Added by TB
	function use_result()
	{
	  if($this->next_result())
	  {
	    $this->result_id=mysqli_store_result($this->conn_id);
	    if($this->result_id)
		$this->result_array=array($this->_fetch_assoc());
	    return TRUE;
	  }
	  else
	    return FALSE;
	}

In Conclusion

The Stored procedures used to retrieve multiple result sets works in the form shown here.

So it is without a doubt that Mysqli and Stored Procedures do work on this webhost.

The fact that CodeIgniter 1.7.2 with my added improvements does not return the 2nd set while the very same code operates successfully on my development machine is probably due to different PHP and possibly MySQL versions.

The Local Machine settings.

PHP Version - 5.2.10-2ubuntu6.4
MySQL Version - Server version: 5.1.37-1ubuntu5.1 (Ubuntu)

Update - It now works in CodeIgniter.

All of the code samples above the CodeIgniter section DO WORK and always did. Just to make that clear...It was what I based my changes to CI upon and created a basis for nice little tutorial on the subject.

In my discussion on the forum I did note that the answer to my alleged problem would be simple and as it turned out, it was.

Some Baffoon (me!) forgot to run the table patch sql file which just so happened to affect the members table. The result was, there was a silent error occurring due to the expected fields in the 2nd result set, not being there. Which resulted in a silent - Empty 2nd result set!

Simple!

As we know, now, well I did know this just not at the time so it appears... That if something works somewhere and not somewhere else, something wasn't done or was done differently.

So all in all, I've got wonderful multiple result sets coming back from my stored procedures that produce them.

Actually , this page was created using some neat templating and the PHP examples shown above the CodeIgniter Section actually run. The results shown from each sample are generated by the actual sample code segments.

So if there is a typo in the sample code - this page will go BANG! with a big PHP Error message. Which it won't cause the code is correct!

Something very good came out of all of this!

Apart from my nifty little code displayer used on this page, we came up with a nice little system during my PHP Class over at Internet Niche Lab - PHP Class in where we came up with a system that let's you enter in PHP code and Run it!

But that's not the nice part! The nice part is, if you make a mistake, instead of it showing a white screen with a PHP Error message, the page gracefully displays in full and shows you the error message in a nice formatted way and lets you correct it. In other words, the broken PHP code doesn't break the page!

It's even got a nice Javascript Syntax Highligther Editor that works on the popular browsers...

That's going to be the basis for a new PHP Tutorial Site! So stay tuned for that.

Further Discussions on this.

There is a forum posting on this up on the CodeIgniter Forum So any comments etc can be posted there.

Thanking you
Tim Brownlaw
Skype: tim_brownlaw
Sokule: http://www.sokule.com/postit/TimBrownlaw