Example of calling mysql stored procedure in PHP program_PHP tutorial

php and mysql are a natural pair. Now I will introduce how to call the stored procedure written in mysql in the php program. Let's take a look at the specific implementation method.

Mysql stored procedure creation syntax

The code is as follows

Copy code

代码如下

复制代码

CREATE PROCEDURE和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

CREATE PROCEDURE and CREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

代码如下	复制代码
Create procedure sp_Name ([proc_parameter ])             routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

代码如下	复制代码
Create functionsp_Name ([func_parameter ])            Returns type     Routine_body

RETURNS type

[characteristic ...] routine_body

代码如下	复制代码
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)

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

After we finish reading, we can start writing some simple stored procedures. First create a stored procedure, Create procedure (subroutine), Create function (function)

The code is as follows	Copy code
Create procedure sp_Name ([proc_parameter ])             routine_body

The parameter type here can be IN OUT INOUTT, which means the same as the meaning of the word. IN means the parameters passed in, OUT means the parameters passed out, and INOUT means the parameters passed in but eventually returned.

The code is as follows	Copy code
Create functionsp_Name ([func_parameter ]) Returns type Routine_body

Returns type specifies the type of return. The type given here must be the same as the type of the return value, otherwise an error will be reported. Here 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 to obtain the current mysql version. So how to combine PHP with the mysql stored procedure.

//The following is 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(20) not null,          Pwd char(32) not null,          Primary key(Id)      );

Drop table if exists user;

Create table user(
Id int unsigned not null auto_increment,

Name varchar(20) not null,

代码如下	复制代码
Delimiter //      Create procedure insertuser(in username varchar(20),in userpwd varchar(32))      Begin          Insert into welefen.user(Name,Pwd) values (username,md5(userpwd));      End      //

Pwd char(32) not null,

Primary key(Id)
);

代码如下	复制代码
Delimiter //      Create procedure validateuser(in username varchar(20),out param1)      Begin           Select Pwd into param1 from welefen.user where Name=username;      End      //

Add user’s stored procedure:

The code is as follows

Copy code

代码如下	复制代码
Delimiter //      Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32))      Begin          Update welefen.user set Pwd=md5(userpwd) where Name=username;      End      //

Delimiter //

Create procedure insertuser(in username varchar(20),in userpwd varchar(32))

Begin

Insert into welefen.user(Name,Pwd) values ​​(username,md5(userpwd));

End

代码如下	复制代码
Delimiter //      Create procedure deleteuser(in username varchar(20)) Begin           delete from welefen.user where Name=username;      End      //

//

Stored procedure to authenticate users:

The code is as follows	Copy code
Delimiter // Create procedure validateuser(in username varchar(20),out param1) Begin           Select Pwd into param1 from welefen.user where Name=username; End //

Stored procedure for changing password:

The code is as follows	Copy code
Delimiter // Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32)) Begin          Update welefen.user set Pwd=md5(userpwd) where Name=username; End //

Delete 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 //

On the client side, we give the following program:

                         
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");//Welefen here are username and password respectively
If (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){
echo "Add user $insert_user[0] successfully
";
}else {
echo "Failed to add user $insert_user[0]
";
}  
                         
$validate_user=array("welefen","welefen");//Welefen here are username and password respectively
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] is verified correctly
";
}else {
               echo "Verification error for user $validate_user[0]
";
}  
                         
$modify_Pwd=array("welefen","weilefeng"); //welefen is the username and weilefeng is the new password
If (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){
                     echo "The password of user $modigy_Pwd[0] has been changed successfully
";
}else {
echo "The password modification of user $modigy_Pwd[0] failed
";
}  
                         
$delete_user=array("welefen"); //welefen is the username
If (mysql_query("call deleteuser('$delete_user[0]')")){
echo "User $delete_user[0] was deleted successfully
";
}else {
                echo "Failed to delete user $delete_user[0]
";
}  
?>

The code is as follows

代码如下

复制代码

if (!mysql_connect("localhost","root","welefen")){ echo "连接数据库失败"; } if (!mysql_select_db("welefen")){ echo "选择数据库表失败 ";      }            $insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码       if (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){           echo "添加用户$insert_user[0]成功 ";      }else {           echo "添加用户$insert_user[0]失败 ";      }            $validate_user=array("welefen","welefen");//这里的welefen分别为用户名、密码      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 "用户$validate_user[0]验证正确 ";      }else {           echo "用户$validate_user[0]验证错误 ";      }            $modify_Pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码       if (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){           echo "用户$modigy_Pwd[0]的密码修改成功 ";      }else {           echo "用户$modigy_Pwd[0]的密码修改失败 ";      }            $delete_user=array("welefen");           //welefen为用户名       if (mysql_query("call deleteuser('$delete_user[0]')")){           echo "用户$delete_user[0]删除成功 ";      }else {           echo "用户$delete_user[0]删除失败 ";      }      ?>

Copy code

This is done. PHP calls the stored procedure of mysql. In fact, these simple applications do not need stored procedures. The actual application is much more complicated than this. It can be seen that establishing the stored procedure of mysql can It greatly reduces the pressure on the customer service side, but increases the pressure on the database service. The various pros and cons must be weighed realistically. http://www.bkjia.com/PHPjc/632923.htmlwww.bkjia.com

true

http: //www.bkjia.com/PHPjc/632923.html

TechArticlephp and mysql are a natural pair. Let me introduce how to call mysql in the php program. The stored procedure has been written. Let’s take a look at the specific implementation method. Mysql storage...