MySql Stored Procedure Invocation from PHP

MySQL ver 5.0 introduced the concept of stored procedures and functions. It is possible to write stored procedures that take parameters from your PHP script and return corresponding rows from the database.

Here is an example of a stored procedure named emp_proc(dept, fy) that takes the department and fiscal year as parameters and returns the first and last names of all matching employees in the employee table:

 

CREATE  PROCEDURE `emp_proc`(dept varchar(10), fy year)
begin

SELECT
lastname, firstname FROM
employee
WHERE dept =  dept AND fiscal_year=fy;

end;

In the MySQL console, this stored proc can be run using the command:

call emp_proc(‘Finance’,’2009′)

This stored proc can be easily called from a PHP script too.. However, a slight change needs to be made to the Mysql connection to make this happen:

Earlier:

mysql_connect($dbhost, $dbuser, $dbpw);

Modified:

mysql_connect($dbhost, $dbuser, $dbpw,true,65536);

Two new parameters need to added to the mysql_connect statement to facilitate invoking stored procs from the script

‘true’ : signifies open a new connection
65536 : Client_Options flag that needs to be used as the last parameter

The following are ALL possible client options – extracted from the MySQL source code:

#define CLIENT_LONG_PASSWORD 1 /* new more secure passwords */
#define CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */
#define CLIENT_LONG_FLAG 4 /* Get all column flags */
#define CLIENT_CONNECT_WITH_DB 8 /* One can specify db on connect */
#define CLIENT_NO_SCHEMA 16 /* Don't allow database.table.column */
#define CLIENT_COMPRESS 32 /* Can use compression protocol */
#define CLIENT_ODBC 64 /* Odbc client */
#define CLIENT_LOCAL_FILES 128 /* Can use LOAD DATA LOCAL */
#define CLIENT_IGNORE_SPACE 256 /* Ignore spaces before '(' */
#define CLIENT_PROTOCOL_41 512 /* New 4.1 protocol */
#define CLIENT_INTERACTIVE 1024 /* This is an interactive client */
#define CLIENT_SSL 2048 /* Switch to SSL after handshake */
#define CLIENT_IGNORE_SIGPIPE 4096 /* IGNORE sigpipes */
#define CLIENT_TRANSACTIONS 8192 /* Client knows about transactions */
#define CLIENT_RESERVED 16384 /* Old flag for 4.1 protocol */
#define CLIENT_SECURE_CONNECTION 32768 /* New 4.1 authentication */
#define CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */
#define CLIENT_MULTI_RESULTS 131072 /* Enable/disable multi-results */
#define CLIENT_REMEMBER_OPTIONS (((ulong) 1) << 31)

Invoking a stored procedure/function from PHP requires the use of the CLIENT_MULTI_STATEMENTS flag.

The final php code that can access the stored procedure is:

<?php
include_once(“config.php”);//all dbconfig parameters go here

mysql_connect($dbhost, $dbuser, $dbpw,true,65536);
mysql_select_db($dbname);

$current_fy = $_POST[‘year’];
$dept = $_POST[‘dept’];

//execute sp on mysql.. note the modified mysql_connect to support this..
$select = “call emp_proc(‘$dept’,’$current_fy’)”;
$result = mysql_query ( $select );
while ($row=mysql_fetch_array($result))
{
//process
}
?>

Advertisements

One thought on “MySql Stored Procedure Invocation from PHP

  1. this post will help me a lot. But i have a problem close this connection, because don`t close with PHP mysql_close($conexion); this persist in MySQL SHOW PROCESSLIST you have any idea to close this connection, i get MySQL error “TOO MANY CONNECTIONS” a lot of times. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s