Using MySQL Stored Procedures and Extending MySQLi in PHP

Date Published: 10/06/2009 14:22 PHP and MySQL Logos

Until recently I had always use the generic MySQL functions included with PHP for connecting to my databases. This stemmed from being taught to connect to databases this way and never really needing an alternative. On a new project I am working on I decided to take a look at the MySQLi (MySQL Improved) library. Most of the functions remain the same but it can now be used in object orientated programming which seemed to me as big advantage. Below I look at implementing MySQLi and extending it with your own custom code, along with using it to execute stored procedures.

Extending MySQLi

MySQLi allows you to create an object which represents your connection to a MySQL database as opposed to creating a connection reference like the older procedural functions. By creating your own class you can extend MySQLi and add customised functionality, better matched for your project. The code below represents extending the MySQLi class without adding any additional variables or functions.

class MySQLDB extends mysqli{

}

$db_connection = new MySQLDB('hostname', 'username', 'password', 'db_name');

Creating Stored Procedures in MySQL

Stored procedures were introduced into MySQL in version 5, but there has been little take up from regular LAMP developers as they are still somewhat unproven. Using stored procedures not only improves security but also reduces traffic between your web application and the database server. To create a stored procedure in MySQL you will need to execute a CREATE PROCEDURE command like the one below.

CREATE PROCEDURE `stpInsertUser`(
   first_name VARCHAR(50),
   last_name VARCHAR(50)
)
BEGIN   
   INSERT INTO `tblUsers`
   (`strFirstName`, `strLastName`)
   VALUES
   (first_name, last_name);
END$$

The above command basically states that there should be a procedure called stpInsertUser created. This procedure should take 2 varchars of length 50 as parameters, one called first_name and the other called last_name. The query to be executed is between the BEGIN and END commands. You may notice the two dollar ($) signs after the END command, this is a custom delimiter. Custom delimiters are used because the standard delimiter, the semi-colon (;) is included in the query to be executed and if the database server executes up until then it will not execute the full CREATE PROCEDURE command. Therefore you must set the delimiter to two dollar signs before this CREATE PROCEDURE is executed. When using phpmyadmin you can define this when you are executing an SQL query (it can be found at the bottom of the query window next to the submit button) but when connecting through another MySQL client you may need to use the following command.

DELIMITER $$

Just remember when using this command to put it back afterwards.

DELIMITER |

Executing Stored Procedures in MySQL

Now we have created our stored procedure we can execute it. To do this we use the CALL command, passing the procedure name and any parameters it may require. To call the procedure defined above we would use the following command.

CALL sdpInsertUser('foo', 'bar');

If this were a query which returns data it would return in the same manner as if you had executed the query directly.

Executing Stored Procedures from MySQLi

With a stored procedure in place we will now extend MySQLi and add a public function called executeStoredProcedure to it. Executing a stored procedure from MySQLi is simply a case of using the CALL command defined above, but we will create a custom method which allows us to customise the execution and check the data it returns. The code below is an extension of the MySQLi class with a new public function for executing stored procedures. The function takes the name of the procedure as a string and an array of the parameters needed for execution. It implodes the parameters string to form the CALL command and returns any data as an array of the rows.

class MySQLDB extends mysqli {

   public function storedProcedure($proc_name, $params){
      $ds = array();
      if ($result = $this->query("CALL $proc_name('" . implode("', '", $params) . "');")) {
         if ($result->num_rows > 0){
            while ($row = $result->fetch_array(MYSQLI_ASSOC)){
               $ds[] = $row;
            }
            $result->close();
         }
         $this->next_result();
      }
      $this->commit();
      return $ds;
   }
}

The above could ofcourse be done without extending the mysqli class, but there are some advantages to doing it this way. For a start you can ensure that every call you make is executed in the same way. Note the $result->close() and $this->next_result() commands which can often be missed, these clear the connection ready for the next query and have caused me a lot of bother. You can also handle the data as you read it from the result object into a format more suitable for your needs. In the past I have created a DataSet object for various reasons, doing this allowed me to return query data in a DataSet object as opposed to just an associative array.

Conclusion

There are definitely some advantages to working with stored procedures and MySQLi. What I have displayed here is just the start of many different things you could do to improve and customise your LAMP web applications. Stored procedures and MySQLi have not as yet thoroughly set themselves in to the LAMP landscape but they are worthwhile improvements. Next time you are developing a LAMP application just have a think how these things may help you. If you have any questions or want help with topics related to this post feel free to comment below and I'll try to help.

Comments

Sorry comments are currently disabled for maintenence

5 Most Recent Articles

Manually Triggering Events in ASP.NET from JavaScript

A quick guide for ASP.NET developers on how to manually trigger ASP.NET events from JavaScript.

Advanced Use of MySQL Stored Procedures

An article for users of MySQL databases describing how they can use advanced stored procedures to improve efficiently in their applications.

Using MySQL Stored Procedures and Extending MySQLi in PHP

A guide for LAMP developers to using stored procedures in MySQL and extending the MySQLi class.

Reading and Writing to Excel Spreadsheets in Python

An introduction to using the xlwt and xlrd modules for python to interact with Microsoft Excel spreadsheets.

Interact with the Web Using Python and the HTTP Library

This is an introduction to making HTTP requests from a python script/application using httplib.

Sponsors