PHP call MySQL stored procedure instance analysis, mysql instance analysis _php tutorial

PHP call MySQL stored procedure instance analysis, MySQL instance analysis

This paper 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, the code is as follows:

Create PROCEDURE and create FUNCTION:
Copy the 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're done, we can start writing some simple stored procedures. , first establish the stored procedure, create PROCEDURE (subroutine), create function (functions), the code is as follows:
Copy Code code is as follows: Create procedure Sp_name ([Proc_parameter])
Routine_body

Here the parameter type can be in the out Inoutt, meaning and the meaning of the word is the same, in means that is passed in the parameters, out is to indicate the outgoing parameters, INOUT is to send in but eventually return the parameters, the code is as follows:
Copy the Code code as follows: Create functionsp_name ([Func_parameter])
Returns type
Routine_body

The Returns type specifies the kind of return, where the given type is the same as the type of the return value, otherwise it will be an error, here is a simple example, the code is as follows:
Copy CodeThe 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 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 CodeThe code is as follows: 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 CodeThe code is as follows: Delimiter//
Create procedure Insertuser (in username varchar), 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 CodeThe code is as follows: Delimiter//
Create procedure ValidateUser (in username varchar (a), out param1)
Begin
Select Pwd into param1 from Welefen.user where name=username;
End
//
To modify the password stored procedure, the code is as follows:
Copy CodeThe code is as follows: Delimiter//
Create procedure Modifypwd (in username varchar), userpwd varchar (32))
Begin
Update Welefen.user set PWD=MD5 (userpwd) where name=username;
End
//
Delete the user's stored procedure with the following code:
Copy CodeThe code is as follows: 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 CodeThe code is as follows: <?php
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, can see, established the MySQL stored procedure can greatly reduce the customer service pressure, but increased the pressure of the database service, Various pros and cons are actually measured.

I hope this article is helpful to everyone's PHP programming.

http://www.bkjia.com/PHPjc/934931.html www.bkjia.com true http://www.bkjia.com/PHPjc/934931.html techarticle PHP calls the MySQL stored procedure instance analysis, the MySQL instance analysis this article analyzes the PHP method which calls the MySQL stored procedure. Share to everyone for your reference. The specific analysis is as follows: MySQL storage ...

