PHP calls MySQL stored procedure instance Analysis _php technique

This article analyzes the method of PHP calling MySQL stored procedure. Share to everyone for your reference. The specific analysis is as follows:

MySQL stored procedure creation syntax, code as follows:

CREATE PROCEDURE and create FUNCTION:

Copy Code code 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

When we have finished, we can start to write some simple stored procedures, first set up a stored procedure, create PROCEDURE (subroutine), create function (function), the code is as follows:

Copy Code code as follows:

Create procedure Sp_name ([Proc_parameter])
Routine_body

The parameter types here can be in-out inoutt, meaning is the same as the meaning of the word, in the expression is the incoming parameter, out is to indicate the outgoing parameter, INOUT is the parameter that passes in but the final return, the code is as follows:

Copy Code code as follows:

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, or an error is made, the following is a simple example, the code reads as follows:

Copy Code code 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 that gets the current version of MySQL. So how does PHP combine with MySQL's stored procedures? The following from Baidu know that the code is as follows:

Copy Code code as follows:

Drop table if exists user;
Create Table User (
Id int unsigned NOT NULL auto_increment,
Name varchar is not NULL,
PWD char (not NULL),
Primary Key (Id)
);

To add a user's stored procedure, the code is as follows:

Copy Code code as follows:

Delimiter//
Create procedure Insertuser (in username varchar (m), in userpwd varchar (32))
Begin
Insert into Welefen.user (name,pwd) VALUES (USERNAME,MD5 (USERPWD));
End
//

Verify the user's stored procedure with the following code:

Copy Code code as follows:

Delimiter//
Create procedure ValidateUser (in username varchar (from), out param1)
Begin
Select Pwd into param1 from Welefen.user where name=username;
End
//

To modify the stored procedure for the password, the code is as follows:

Copy Code code as follows:

Delimiter//
Create procedure Modifypwd (in username varchar (m), in 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 code as follows:

Delimiter//
Create procedure DeleteUser (in username varchar (20))
Begin
Delete from Welefen.user where name=username;
End
//

In the client, we give the following procedure, the code is as follows:

Copy Code code as follows:

<?php
if (!mysql_connect ("localhost", "root", "Welefen")) {
echo "Failed to connect to database";
}
if (!mysql_select_db ("Welefen")) {
echo "Select database table failed <br>";
}

$insert _user=array ("Welefen", "Welefen");/the Welefen here are username, password
if (mysql_query ("Call Insertuser (' $insert _user[0] ', ' $insert _user[1]")) {
echo "Add user $insert_user[0] Success <br>";
}else {
echo "Add user $insert_user[0] Failed <br>";
}

$validate _user=array ("Welefen", "Welefen");/the Welefen here are username, 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] Verify correct <br>";
}else {
echo "User $validate_user[0] validation error <br>";
}

$modify _pwd=array ("Welefen", "Weilefeng"); Welefen for user name weilefeng new password
if (mysql_query ("Call Modifypwd (' $modify _pwd[0] ', ' $modify _pwd[1]")) {
echo "User $modigy_pwd[0] Password modification success <br>";
}else {
echo "User $modigy_pwd[0] Password modification failed <br>";
}

$delete _user=array ("Welefen"); Welefen for User name
if (mysql_query ("Call DeleteUser (' $delete _user[0] ')") {
echo "User $delete_user[0] Delete success <br>";
}else {
echo "User $delete_user[0] Delete failed <br>";
}
?>

So it's done, PHP calls MySQL stored procedures, in fact, these simple applications, the use of stored procedures, the actual application is more complex than this, you can see that the establishment of the MySQL storage process can greatly reduce the pressure of customer service, but increased the pressure of database services, All the pros and cons are actually measured.

I hope this article will help you with your PHP program design.