PHP calls mysql stored procedure instance analysis, mysql instance analysis
This article analyzes the method of calling mysql stored procedures in PHP. Share it with everyone for your reference. The specific analysis is as follows:
Mysql stored procedure creation syntax, the code is as follows:
CREATE PROCEDURE and CREATE FUNCTION:
Copy code The code is as follows:
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
After we finish reading, we can start writing some simple stored procedures. First, create the stored procedures, Create procedure (subprogram) and Create function (function). The code is as follows:
Copy code The code is as follows:
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 The code is as follows:
Create functionsp_Name ([func_parameter])
Returns type
Routine_body
Returns type specifies the return type. 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 The code is as follows:
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 does PHP combine with the mysql stored procedure? The following is from Baidu, the code is as follows:
Copy code The code is as follows:
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)
);
Add the user's stored procedure, the code is as follows:
Copy code The code is as follows:Delimiter //
Create procedure insertuser(in username varchar(20),in userpwd varchar(32))
Begin
Insert into welefen.user(Name,Pwd) values (username,md5(userpwd));
End
//
Verify the user's stored procedure, the code is as follows:
Copy code
The code is as follows:Delimiter //
Create procedure validateuser(in username varchar(20),out param1)
Begin
Select Pwd into param1 from welefen.user where Name=username;
End
//
The stored procedure for changing the password is as follows:
Copy code The code is as follows:
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 the user's stored procedure, the code is as follows:
Copy code The code is as follows: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, the code is as follows:
Copy code The code is as follows:
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 change 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]
";
}
?>
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 greatly It 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.
I hope this article will be helpful to everyone’s PHP programming design.
http://www.bkjia.com/PHPjc/934931.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/934931.htmlTechArticlePHP calls mysql stored procedure instance analysis, mysql instance analysis This article analyzes the method of php calling mysql stored procedure. Share it with everyone for your reference. The specific analysis is as follows: Mysql storage...
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
