If you want to do something related with database you send a query to database. It may be update, insert or delete query or select query to read some data from database. Also some time you need to do some controllers before you update a row. Then you need to send 2 query to your database and it increases client-server traffic and your website works slowly. At this point you can use stored procedure.

Stored Procedures are kind of function that keeps many query and transaction inside but it does them all at server and you just send a query to server. Thus it works fast and also using stored procedures are secure.

To be able to create and use stored procedures you need to know SQL very well. Also you should know that current phpmyadmin doesn’t support stored procedure syntaxes.  So you should be able to connect your database with some database software like navicat.

Lets see some examples of stored procedures.

BEGIN

SELECT * FROM Tables;

END

This is basic stored procedure that pulls rows from any table you want. To call this stored procedure with php we send such query to database.

Query("CALL stored_procedure_name ()")

As you see it is easy. We can send some parameters and pulls any value we want by using variables in stored procedure.

For example we have stored procedure named login and we get 2 parameters userid and password:

LOGIN(uid TEXT, pwd TEXT)

BEGIN

DECLARE check INT;

DECLARE cdate DATETIME;

SET cdate = NOW();

SELECT COUNT(*) as check FROM users WHERE userid = uid AND password = pwd;

IF check = 1 THEN

UPDATE users SET logindate = cdate WHERE userid = uid;

END IF;

SELECT check;

END

I want to explain this stored procedure. We need to send two variable to run this stored procedure. To define a variable in stored procedure we use DECLARE variablename and variable type (INT, TEXT, DATETIME, etc…). To appoint value to variable we use SET variablename = variable value. Other parts are normal sql transactions. If we want to return specific value, we need to use SELECT variablename(s).

Query("CALL LOGIN('userid','userpassword')");

As a result of this query we will get 1 (true) or 0 (false).

As I said before you need to know sql to be able to use stored procedures but I want to show one last thing: paging.

Paging is not same as you use in php. You need to use specific format in stored procedure like :

SEARCH(srcText, page)

BEGIN

DECLARE src TEXT;

DECLARE p INT;

SET @src = srcText;

SET @p = page*10;

PREPARE result FROM "

SELECT * FROM table WHERE field LIKE '%?%' LIMIT ?,0";

EXECUTE result USING @src, @p;

END

As you see you need to define specific variables and then in query you need to use ? Instead of variable. Then, with EXECUTE you define instead of ? Which variable will be used.

For any question about stored procedures please visit bounmis.com