Biography:

Location: Dayton, OH
College: Cedarville University, Cedarville, OH
Degree: B.A. Management Information Systems (2002)
Certifications: Microsoft Certified Professional (MCP) 2002

Interests:

Music, Playing Guitar, Working on Cars, Home Improvement, Photography, Computers, Website Programming, Driving in the snow, NHL Hockey, Sand Volleyball, Settlers of Catan board game

Favorites:

Foods: Lasagna, Carrot Casserole, Chicken Parmesan
Sports: Hockey, Football, Volleyball, Soccer
Desserts: Peanut Butter Pie, Peanut Butter Passion ice cream

Employment Information:

Current Resume: Download PDF Resume


Contact Me:
Use this area to send me a note or message:
Name:
Email:
Comments:
Security Code:

Fun links you should probably check out:


Fun games you might want to play:


Current Code Samples:


Future Code Samples:

  • LINQ-to-XML Dynamic Where Clauses Using Ternary Operators
  • C# List delegate alphanumeric sort
  • Using HTML form input arrays with ColdFusion 7
  • Capture selected text or cursor position in a TEXTAREA with JavaScript
  • Show/Hide all SELECT input items on a page with JavaScript
  • Show/Hide element with opacity level fading using JavaScript
  • CAPTCHA form-protection of images using PHP and GD 2.0 library
  • Resizing images using PHP and the GD 2.0 library
  • Rotating images using PHP and the GD 2.0 library
  • Cropping images using JavaScript, PHP, and the GD 2.0 library
  • Add image watermarks using PHP and the GD 2.0 library
CODE TUTORIAL: MySql Stored Procedures
I've been out of practice with PHP/MySQL for about 18 months or so, and am getting back into it more heavily with a new website project I'm taking on

For this newest web project, I'll be constructing an e-commerce store that will retail WWI / WWII / Civil War collectibles and apparel based on a MySQL 5 and PHP 5 server tier. SQL Server 2005 stored procedures are an old hat to me, but using stored procedures with MySQL 5 is something new for me. I've made up some PHP / MySql code samples below to help get you started on your way to writing / editing / troubleshooting stored procedures.

Useful Tools:
  1. SpEditor (freeware from ChadLloyd)
  2. MySQL v5.0 or higher
  3. PHP v5.0 or higher

Chad Lloyd's great (and free) SpEditor is a great and easy tool to use for creating stored procedures. (You ask, why would you need that when I have MySQL Administrator app that I can run locally from my PC, or when I have phpMyAdmin running to do all of my database work with?) Unfortunately, as of this writing, phpMyAdmin does NOT properly handle MySQL Stored Procedures. You can write proper syntax CREATE or ALTER statements and phpMyAdmin will fail to validate/parse your queries before you are even allowed to insert them into MySQL. MySQL Administrator works great, but some web hosting providers DO NOT allow external client connections to their database servers. (1and1.com is a web hosting provider that has this restriction)

Downloading Chad's .php script will provide a very basic interface for creating / editing / deleting stored procedures from your MySQL 5 database server. Setting up the script is a cinch after modifying some connection parameters and setting up an .htaccess file to protect the directory from unethical or devious visitors. Provided below is a sample .htaccess file I wrote and placed in the same folder as Chad's speditor.php script on my webserver:
.htaccess CODE: ".htaccess"
AuthName "Protected Code Area"
AuthType Basic
<Limit GET POST>
order deny,allow
deny from all
allow from 24.33.XXX.XXX
allow from 74.218.XXX.XXX
</Limit>
This effectively denies HTTP request to this .php script from ALL IP addresses other than the (2) that I have set to allow. This step will protect someone from being able to maliciously delete all of your stored procedures you've painstakingly created.

Step 1: Creating a basic Stored Procedure

Let's create a MySQL stored procedure called "usp_simpleSelect" with the SPEditor tool:


As you can see, the UI is pretty basic (but it's nice to only have 1 php file to deal with as part of the SPEditor installation). You'll notice I called my new stored procedure "usp_simpleSelect". My SQL Server 2005 naming conventions are coming over here to MySQL with the prefix of "usp_" which signifies that this is a user stored procedure. You'll also notice in the screenshot above that some of the MySQL syntax for creating a new stored procedure is automatically filled out for you.

Ok, let's move along and write some SQL in order to get a dataset of results:


Clicking the "Update" button at the bottom of the form will then create this new stored procedure in your specified MySQL database.

So you we want to execute/call this new stored procedure, to see what data is returned. To do this, we choose from the convenient dropdown menu the stored procedure we want to use. Next, we select "Call" as our option from the 2nd dropdown menu. Clicking the "Go" button will refresh the page (Whenever you call a stored procedure that doesn't have any input parameters, SPEditor will just place a "Call" button on the page that you will have to click to see your dataset of results:


Here is the dataset of results returned by our simple MySQL stored procedure:



Step 2: Creating a Stored Procedure with an INPUT Variable


Adding INPUT variables to stored procedures is almost a necessary if not mandatory task for most database programmers. Here is a screenshot of a new stored procedure I wrote that will accept an Apparel_ID INPUT variable and will return the single row that matches that value:


Now let's run this stored procedure, entering in our Apparel_ID input variable as "1":


Here is the dataset of results returned by the stored procedure:



Step 3: Using PHP to call a MySQL Stored Procedure


So now we want to add some PHP code to call this MySQL stored procedure and use it to output values to our HTML pages. This can be somewhat confusing, but for starters, make sure you are running PHP 5.0+ or better for purposes of this tutorial.

First, let's create a PHP include file that will contain our DB connection information in it:
PHP CODE: "connect.php"
<?
  //  This file is used to connect to the MySQL database
  function ConnectDB()
  {
    $MySQL_Username = "MySqlUser";
    $MySQL_Password = "MySqlPass";
    $MySQL_Host = "dbserver.domain.com";
    $MySQL_Database_Name = "MySqlDbName";
    
    $link = mysqli_connect ($MySQL_Host, $MySQL_Username, $MySQL_Password) or die("Could not connect to the $MySQL_Database_Name database on $MySQL_Host<br>".mysqli_error($link));
    
    //  Try to select Database
    if (!mysqli_select_db($link, $MySQL_Database_Name))
    {
      //  If Database cannot be selected, try to create it, then select it.
      $query = "CREATE DATABASE ".$MySQL_Database_Name;
      echo $query."<br>";
      $result = mysqli_query($link, $query) or die ('Error creating database: '.$MySQL_Database_Name."<br>".mysqli_error()."<br><br><br><b>You may have to manually create a MySQL database on your webserver to continue because of the permissions that are set on your account.</b>");
      if ($result)  //  If CREATE DATABASE WORKED
      {  
        mysqli_select_db($link, $MySQL_Database_Name) or die ('Error selecting database: '.$MySQL_Database_Name."<br>".mysqli_error());      
      }
    }
    return $link;
  }
?>

Next, create another PHP page to use for calling the stored procedure called "sp_test.php". This page will need to use the PHP include_once() method to ensure that the code you added into "connect.php" will exist at the top of this page. Here is some simple PHP code that will allow you to call a stored procedure and retrieve the results to your PHP page:
PHP CODE: "sp_test.php"
<?
  //  These two lines of code will include your MySQL db connection information on this page
  include_once("connect.php");
  $link1 = ConnectDB();  
  
  //  Here we attempt to use $link1 and call the stored procedure named "usp_simpleSelect()"
  if ($result = mysqli_query($link1, "call usp_simpleSelect(1)"))
  {
    echo ("<div>\n");
    //  Fetch the results of the query
    while($row = mysqli_fetch_array($result))
    {
      echo ("<p>".$row[0]." - ".$row[1]." - ".$row[2]." - ".$row[3]."\n");
    }
    echo ("</div>\n");
    
    //  Destroy the result set and free the memory used for it
    mysqli_free_result($result);
  }
  else
  {
    echo mysqli_error($link1)."<br>";
  }
  //  Close the connection
  mysqli_close($link1);
?>


Adding more arguments to your MySQL stored procedure is quite easy, you can simply modify the "call" statement in PHP to something like the following:
PHP CODE: "sp_test.php"
<?
  //  Here we attempt to use $link1 and call the stored procedure named "usp_simpleSelect()"
  $result = mysqli_query($link1, "call usp_simpleSelect(1, 2, 3, 4, 5)");
?>



Step 4: Using PHP to call a MySQL Stored Procedure (with multiple returned recordsets)


Retrieving multiple recordsets with a single stored procedure is a task that almost every DB developer should be familiar with. Combining recordsets that need to be retrieved from the database into a single DB call is much faster, more secure, and more efficient for the server to handle. You can see gains of 50% or more by combining multiple separate queries into a single stored procedure that returns multiple recordsets.

First, we'll create the MySql stored procedure that will return multiple recordsets. As you can see, my 1st recordset will use the INPUT variable Apparel_ID, but the 2nd recordset will not use any INPUT variables, and the 3rd recordset will again use the INPUT variable Apparel_ID.


Next, we want to call this MySql stored procedure with an Apparel_ID of 1 to test it out:


Here are the recordsets returned by the database for Apparel_ID 1:



You'll notice that Chad's SPEditor does a good job of numbering and showing you the recordsets that are returned by the stored procedure. Now that we are sure the stored procedure works as advertised, we should probably write some PHP code that can handle multiple recordsets for us, and put that code into a PHP function so we can re-use it at any time:
PHP CODE: "sp_functions.php"
<?
  //  This line of code will include your MySQL db connection information on this page
  include_once("connect.php");
  
  function retrieveRecordsetsFromStoredProcedure($SP_To_Call, $Parameters)
  {
    $link0 = ConnectDB();
    $query = "call $SP_To_Call($Parameters)";
    $resultset = null;
    
    //  Perform a multiple recordset query
    if (mysqli_multi_query($link0, $query))
    {  
      $resultset = array();
      $i = 0;
      do
      {
        //  Store the recordset we are on in our $result variable
        $result = mysqli_store_result($link0);
        if ($result)
        {
          //  Fetch the entire current recordset into the $resultset
          $resultset[$i] = mysqli_fetch_array($result);
  
          //  Destroy the result set and free the memory used for it
          mysqli_free_result($result);
          $i++;
        }
        //  Fetch the next result
      } while (mysqli_next_result($link0));    
    }
    else
    {
      echo mysqli_error($link0)."<br>";
    }
    //  Close the connection
    mysqli_close($link0);
    
    return $resultset;
  }
?>
There you have it. How to write MySQL stored procedures and use PHP to get the results in a fairly easy tutorial.

- Justin Tubbs