PHP program call MySQL stored procedure Instance _php tutorial

PHP and MySQL are inherently a pair, let me explain how to call in the PHP program in MySQL has been written in the stored procedures, we look at the implementation of the specific method.

MySQL Stored procedure creation syntax

The code is as follows

Copy Code

CREATE PROCEDURE and create FUNCTION CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [Characteristic ...] Routine_body CREATE FUNCTION sp_name ([func_parameter[,...]) RETURNS type [Characteristic ...] Routine_body Proc_parameter: [In | Out | INOUT] Param_name Type Func_parameter: Param_name type Type Any valid MySQL data type Characteristic: LANGUAGE SQL | [NOT] Deterministic | {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA} | SQL SECURITY {definer | INVOKER} | COMMENT ' String ' Routine_body: Valid SQL PROCEDURE statement or statements

When we are finished, we can start to write some simple stored procedures.

First create a stored procedure, create PROCEDURE (subroutine), create function (functions)

The code is as follows	Copy Code
Create procedure Sp_name ([Proc_parameter]) Routine_body

The type of the argument here can be in the out Inoutt, meaning and the meaning of the word is the same, in indicates that is passed in the parameters, out is to indicate the outgoing parameters, INOUT is to represent the passed in but eventually returned parameters.

The code is as follows	Copy Code
Create Functionsp_name ([Func_parameter]) Returns type Routine_body

The Returns type specifies the returned types, where the given type is the same as the type of the return value, otherwise an error is made.

The following is a simple example:

The code is as follows	Copy Code
Mysql> delimiter// Mysql> CREATE PROCEDURE G Begin -Select version () I -End // Query OK, 0 rows affected Mysql> Call GetVersion (@a // Query OK, 0 rows affected Mysql> Select @a; // +---------------------+ | @a | +---------------------+ | 5.0.45-community-nt | +---------------------+ 1 row in Set (0.05 sec)

A stored procedure that gets the current MySQL version. So how does PHP combine with the MySQL stored procedure?

The following from Baidu know:

The code is as follows	Copy Code
Drop table if exists user; Create Table User ( Id int unsigned NOT NULL auto_increment, Name varchar () is not NULL, PWD char (+) is not NULL, Primary Key (Id) );

To add a user's stored procedure:

The code is as follows	Copy Code
Delimiter// Create procedure Insertuser (in username varchar), userpwd varchar (32)) Begin Insert into Welefen.user (name,pwd) VALUES (USERNAME,MD5 (USERPWD)); End //

To validate a user's stored procedure:

The code is as follows	Copy Code
Delimiter// Create procedure ValidateUser (in username varchar (a), out param1) Begin Select Pwd into param1 from Welefen.user where name=username; End //

To modify a password stored procedure:

The code is as follows	Copy Code
Delimiter// Create procedure Modifypwd (in username varchar), userpwd varchar (32)) Begin Update Welefen.user set PWD=MD5 (userpwd) where name=username; End //

To delete a user's stored procedure:

The code is as follows	Copy Code
Delimiter// Create procedure DeleteUser (in username varchar (20)) Begin Delete from Welefen.user where name=username; End //

At the client, we give the following program:

The code is as follows

Copy Code

if (!mysql_connect ("localhost", "root", "Welefen")) { echo "Failed to connect to database"; } if (!mysql_select_db ("Welefen")) { echo "Failed to select database table "; } $insert _user=array ("Welefen", "Welefen");//The welefen here are user name, password if (mysql_query (' Call Insertuser (' $insert _user[0] ', ' $insert _user[1] ')) { echo "Add user $insert_user[0] Success "; }else { echo "Add user $insert_user[0] Failed "; } $validate _user=array ("Welefen", "Welefen");//The welefen here are user name, password mysql_query ("Call ValidateUser (' $validate _user[0] ', @a)"); $PWD =mysql_query ("select @a"); $result =mysql_fetch_array ($PWD); if ($result [0]==md5 ($validate _user[1])) { echo "User $validate_user[0" verified correctly "; }else { echo "User $validate_user[0" validation error "; } $modify _pwd=array ("Welefen", "Weilefeng"); Welefen the user name Weilefeng to the new password if (mysql_query (' Call Modifypwd (' $modify _pwd[0] ', ' $modify _pwd[1] ')) { echo "User $modigy_pwd[0" password modified successfully "; }else { echo "User $modigy_pwd[0" password modification failed "; } $delete _user=array ("Welefen"); Welefen for User name if (mysql_query ("Call DeleteUser (' $delete _user[0] ')") { echo "User $delete_user[0" Delete succeeded "; }else { echo "User $delete_user[0" Delete failed "; } ?>

This completes, PHP calls the MySQL stored procedure, actually these simple application, does not have the stored procedure, the actual application is more complex than this. As can be seen, the establishment of the MySQL storage process can greatly reduce the pressure on the customer service side, but increased the pressure of the database services, the pros and cons are actually measured.

http://www.bkjia.com/PHPjc/632923.html www.bkjia.com true http://www.bkjia.com/PHPjc/632923.html techarticle PHP and MySQL are inherently a pair, let me explain how to call in the PHP program in MySQL has been written in the stored procedures, we look at the implementation of the specific method. MySQL storage ...

