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

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:


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:

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:
There you have it. How to write MySQL stored procedures and use PHP to get the results in a fairly easy tutorial.

- Justin Tubbs