PHP call MySQL stored procedure Member Login Verification Example Analysis

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:

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 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:

The code is as follows	Copy Code
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:

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

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&n bsp;                 | +---------------------+ | 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:

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 (not NULL), Primary Key (Id) );

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

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows

Copy Code

<?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.