MySQL Stored Procedures - Part 1

It seems very hard to find sample examples of MySQL stored Procedures on the internet. So, I have decided to create a blog on creating procedures in MySQL.

In this part I am going to explain how to create a simple procedure and using it in your PHP code.

You can create it using a 3rd party tool like SQLYog or by connectin to your MySQL server in your command prompt.
mysql> delimiter //
mysql> DROP PROCEDURE IF EXISTS `MyProcedure` //
mysql> CREATE PROCEDURE MyProcedure (OUT Total INT)
-> BEGIN
-> SELECT COUNT(*) INTO Total FROM tab_name;
-> END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter;

mysql> CALL MyProcedure(@BlogCount);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @BlogCount;
+--------------+
@BlogCount
+--------------+
3
+--------------+
1 row in set (0.00 sec)




This is just about how to create a procedure in MySQL.

The below code will show you how to use this procedure in PHP code.

After connecting to the database you can use this procedure using below code:

mysql_query('CALL MyProcedure(@TotalBlogCount)');

This is not the end of it, you have to query the database again to fetch the @TotalBlogCount, here is the simple PHP code consuming the above procedure...


<?
   $link = mysql_connect('localhost','root','root') or die('Could not connect to the server');
   mysql_select_db('your_db_name',$link);

   $res = mysql_query('CALL MyProcedure(@Total)') or die(mysql_error());
   if($res)
   {
      $ActualRes = mysql_query('SELECT @Total') or die(mysql_error());
      if($ActualRes)
      {
         $Row = mysql_fetch_assoc($ActualRes);
         print_r($Row);
      }
   }

?>

The result will be

Array
(
[@Total] => 295
)

I hope this will give you a basic idea to create procedures in MySQL.