Saturday, August 18, 2007

Store procedure usage in PHP

Stored routines (procedures and functions) are supported in MySQL 5.1. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

A stored routine is either a procedure . Stored routines are created with CREATE PROCEDURE statements. A procedure is invoked using a CALL statement, and can only pass back values using output variables. Stored routines may call other stored routines.
A stored procedure or function is associated with a particular database.

Procedures can be created using CREATE PROCEDURE command of mysql.
To know the exact syntax visit Online MySql documentation.

Once the procedure is created we have to use it in php code for this PHP's mysqli extension allows you to access the functionality provided by MySQL 4.1 and above

In order to have these functions available, you must compile PHP with support for the mysqli extension(MySQL Improved Extension).

To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1.

mysqli functions are similar to mysql functions, there is substitute for each mysql functions in mysqli.
For e.g.

mysqli_query() has to be used instead of mysql_query()

using mysqli_query() the stored procedure query can be called and its result can be fetched.

Note: if you have to use any of mysqli functions then you will have to use mysqli functions throughout the current mysql connection
i.e. from mysqli_connect() to mysqli_close() all intermediate functions used must be of mysqli and not mysql.


No comments: