Stored Procedures with MySQL

Most enterprise grade databases support stored procedures, which have two essential advantages. First, stored procedures reside in the database (such as tables and indexes) and the database queries in the stored procedures are executed at the database side. It yields faster results because such queries are optimized by the database (by pre-planning the query(ies) execution. The other advantage is that the stored procedures allow you to realize the three-tier framework in your application. In a data-intensive application, stored procedures can incorporate the entire business logic in them. Stored procedures do support minimal programming constructs (like conditions and loops) that can be used to implement the business logic. Thus, we can have a desktop as well as a Web-based application, which have different presentations but both will call the same stored procedures, lying at the database. If the business logic changes, then the changes need to be made only at one place-in the stored procedures. 

MySQL does not support stored procedures, till date. But the forthcoming version 5 will support them. Also the latest version of PHP (version 5 and above) supports calling of stored procedures in MySQL 5. In this article, we will see the glimpse of stored procedures in MySQL and calling them through PHP. We will install and set up MySQL and PHP on PCQLinux 2005. 

Install MySQL 5



Go to http://dev.mysql.com/downloads/mysql/5.0.html.

Download the RPM files for the Server, Client programs and Libraries, and header files from the section 'Linux x86 RPM downloads'. Install the RPMs using the

rpm -Uvh command. If you get any dependency errors, suffix 'nodeps' to the above command. Next, start the MySQL server as:

/etc/init.d/mysql start

Note: MySQL 5 is a beta version and hence we don't recommend installing it on a production server. 

The default installation of MySQL allows anonymous logins from the localhost and has the root (root MySQL user and not the Linux root user) password set to blank. To set up a password for the root MySQL user, issue:

mysqladmin -u root password passwd

Substitute passwd with your preferred password. To delete anonymous logins, issue:

mysql -u root -p

When prompted, supply the password that you had specified in the above step. You will be dropped into a mysql> prompt. Issue the following at this prompt.

use mysql;



delete from user where user='';

Install PHP 5



For a running Apache Web server that comes with PCQLinux 2005, download the latest release of

PHP 5 (5.0.4 now) from http://www.php.net/get/php-5.0.4.tar.bz2/from/a/mirror.

Extract the downloaded archive and change to the resulting directory. Now issue:

#./configure --with-mysqli=/usr/bin/mysql_config -with-#apxs2



#make


#make install

Fire up a text editor and type in the following. 

AddType application/x-httpd-php .php



DirectoryIndex index.php

Save the file as php.conf in /etc/httpd/conf.d. In case you have installed PHP that comes with PCQLinux 2005, you will already have a file named php.conf in this directory. In this case, open the file and comment the following line by prefixing a #.

LoadModule php4_module modules/libphp4.so

Restart Apache Web server.

/etc/rc.d/init.d/httpd restart

A stored procedure 



Next we write a stored procedure to select a particular record from the user table of the pre existing database named mysql. The user table contains the records for the database users. In the stored procedure we will retrieve and display the record for a particular user. The table will have entry for only root user (since we already deleted anonymous users). Now issue:

mysql -u root -p

When prompted, supply the password you had set up for the MySQL's root user. At the mysql> prompt issue:

use mysql



delimiter .

Note the trailing dot (.) after delimiter. Next, issue:

create procedure sp_getrecordbyuser (IN usr varchar(255))



select user,host from user where user=usr;

Note the dot (.) in the last line. We have created a stored procedure named sp_getrecordbyuser, which accepts a parameter named usr of type varchar (alphanumeric). The stored procedure then runs a query to select records where the user is equal to the value passed as the parameter (as specified by where user=usr). At the mysql prompt you can call the stored procedure by issuing the following command.

call sp_getrecordbyuser('root')

This should show two records from the database. 

Call from PHP



We have, thus, created a stored procedure and called it at the mysql prompt. Now, we will write a PHP script that will call the stored procedure and display the result in a HTML table. 

$conn = mysqli_connect("localhost","root","passwd");



mysqli_select_db ($conn,"mysql");


$query_result = mysqli_query($conn,"call sp_getrecordbyuser('root')")


?>

while($row = mysqli_fetch_array($query_result, MYSQLI_ASSOC)) {


?>

?>

Substitute passwd in the first statement with the password for the MySQL's root user in your case. The methodology to connect to a MySQL database and querying it remains the same. Though instead of using the traditional PHP's MySQL functions we have used the new mysqli functions. 

Note that the function names remain the same expect for the 'i' suffix to mysql everywhere. For more information on mysqli, refer to the URL

http://php.net/mysqli.  

Shekhar Govindarajan



IT4Enterprise