Executing Stored Procedures and Functions from PHP/MySQL

Share with your friends


php_and_mysqlBefore we discuss the procedure to run stored procedures and functions which are latest features of MySQL 5, let’s learn the fundamental difference between them and the advantages of both. I would recommend the programmers to first clear the concept of subroutines and functions in PHP, as then only they can understand these new features very well.

What’s the fundamental difference between stored procedures and stored functions?

A stored procedure (as similar to subroutine) calls some code whereas a stored function (as similar to function) calls some code and then showcases a result.

Key benefits of Stored Procedure:

  • Need to be developed once as all application can use same command
  • Step down the complexity level of the code
  • Network traffic decreases to a considerable level as to and fro from server is reduced, which in turn decrease the CPU load
  • Command centralization makes repeated call easy
  • Executes in any environment

Key benefits of Stored Functions:

  • Runs at the server side, thus take advantage of its higher processing power and access the data stored on the local drive of the server
  • Stored in MySQL database, so they are reusable and share-able

How to execute Stored Procedure?

In order to run a stored procedure in MySQL and PHP application, you need to use ‘Call SQL statement’ as shown below:

$a = mysql_query('call StoredProc()');

Three parameters are used to write a call statement in MySQL: Input Parameters (IN), Output Parameters (OUT), and Input and Output Parameters (INOUT).

IN Parameter: It passes a value into a procedure. The call statement (or procedure) may alter the value, which might not be evident to the caller when it returns.

OUT Parameter: It returns a value to the caller from the procedure. Its initial value is Null and final value is visible, as the procedure modifies the value in between.

INOUT Parameter: Initialized by the caller, the INOUT parameter changes the value such that the final value of the procedure is visible when it returns. 

How to execute stored function?

As similar to any other MySQL query, stored function in PHP application can be executed using following syntax:

mysql_query(‘SELECT yourFunction()’);

Note:  You need not to specify the parameter as IN, OUT or INOUT, as all parameters within a function are IN parameters.

Using stored procedure and functions in PHP application, the developers can greatly reduce the complexity of the code and speed up the compiling process. These attributes help you cut off the application from back-end changes in the database, thus there are lesser chances of errors. Hire PHP developer India to benefit from the cutting-edge features of MySQL 5 and PHP, that too without hurting your pockets.

Summary: Stored procedures and stored functions simplify the code, speed up the development and shield the PHP application from the database modifications, meaning the errors will be reduced. A PHP Development India Company stays abreast with the updates and offer top-quality solutions at low prices.