Detailed PHP call MySQL stored procedure method

Source: Internet
Author: User
Tags php mysql

MySQL from the beginning of 5.0 to introduce the stored procedures, anyway, never touched before, but now because the main internal system, so many applications are used to the stored procedures, of course, the front sometimes need to call the MySQL stored procedures, php MySQL Lib seems to support is not very good, But I searched some information, although not many, but still try to use, now introduce the method


1. The method that invokes the stored procedure.


A. If the stored procedure has in/inout parameters, declare a variable, input parameters to the stored procedure, the variable is a pair of PHP variables (also can not, just do not have PHP variables, there is no way to dynamic input), a MySQL variable.


B. If the stored procedure has an out variable, declare a MySQL variable. MySQL variable declaration is more special, you must let the MySQL server know the existence of this variable, in fact, is the implementation of a MySQL statement. into set @mysqlvar = $phpvar;


C. Use mysql_query ()/mysql_db_query () to execute the MySQL variable declaration statement.

The code is as follows Copy Code

mysql_query ("set @mysqlvar" = $pbpvar "");


In this way, there is a variable in the MySQL server, @mysqlar. If the time in parameter, then its value can have phpar passed in.


D. If the stored procedure is.


1. Executes the call procedure () statement.


That is mysql_query ("Call Proceduer ([var1] ...)");

2. If there is a return value, execute the Select @ar and return the execution result.

The code is as follows Copy Code
mysql_query ("Select @var)"


The next action is the same as PHP executing a general MySQL statement. Results can be obtained through functions such as Mydql_fetch_row ().


Here's a summary of some stored procedures that call an instance of a stored procedure without parameters

The code is as follows Copy Code


$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");
mysql_select_db (' Test ', $conn);
$sql = "
CREATE PROCEDURE Myproce ()
Begin
INSERT into user (ID, username, sex) VALUES (NULL, ' s ', ' 0 ');
End
";
mysql_query ($sql);//Create a Myproce stored procedure

$sql = "Call Test.myproce ();";
mysql_query ($sql);//Call Myproce stored procedure, a new record will be added to the database.

Type one: calling methods with input and output type parameters

The code is as follows Copy Code

$returnValue = ';
try {
mysql_query ("Set @Return");
$spname = ' p__test_getinfo1 ';
mysql_query ("Call $spname (@Return, ' {$userId} ', ' {$pwd} ')") or Die ("[$spname]query failed:". Mysql_error ());
$result _return = mysql_query ("select @Return");
$row _return = mysql_fetch_row ($result _return);
$returnValue = $row _return [0];
catch (Exception $e) {
Echo $e;
}
Echo $returnValue; Output variables from the stored procedure

Type two: Calling methods with multiple output types and multiple input type parameters

  code is as follows copy code

$userId = 0;
try{
mysql_query ("Set @Message");
mysql_query ("Set @Id");
mysql_query ("Call P__test_login (@Message, @Id, ' {$userId} ', ' {$pwd} ')", $conn) or Die ("Query failed:". Mysql_error ());
$result _mess = mysql_query ("select @Message");
$result _uid = mysql_query ("select @Id");
$row _mess = mysql_fetch_row ($result _mess);
$row _uid = mysql_fetch_row ($result _uid);
$Proc _error = $row _mess[0];
$uId = $row _uid[0];
}
catch (Exception $e)
{
Echo $e;
}
Echo ' proc return message: ' $Proc _error. ' <br/> '; Output variables from the stored procedure
echo ' User ID: '. $uId; Get User ID

Type three: Calling a method with a return result set

The code is as follows Copy Code

try {
$spname = ' P__test_getdata ';
$query = mysql_query ("Call $spname ()", $conn) or Die ("[$spname]query failed:". Mysql_error ());
while ($row = Mysql_fetch_array ($query)) {
echo $row [' Provinceid ']. ':: '. $row [' Provincename ']; Output data Set
}

catch (Exception $e) {
Echo $e;
}

Type IV: Calls a method with return multiple result sets (currently only implemented through MYSQLI ~ ~)

The code is as follows Copy Code

Php
$rows = Array ();
$db = new Mysqli ($server, $user, $PSD, $dbname);
if (Mysqli_connect_errno ()) {
$this->message (' Can not connect to MySQL server ');
}
$db->query ("SET NAMES UTF8");
$db->query ("SET @Message");
if ($db->real_query ("Call P__test_getdata2 (@Message)")) {
do{
if ($result = $db->store_result ()) {
while ($row = $result->fetch_assoc ()) {
Array_push ($rows, $row);
}
$result->close ();
}
}while ($db->next_result ());
}
$db->close ();
Print_r ($rows);
Procedure
......
SELECT * FROM T1 where ...
SELECT * FROM T2 where ...
......

Instance four: InOut stored procedures for outgoing parameters

The code is as follows Copy Code
$sql = "
CREATE PROCEDURE Myproce4 (inout Sexflag int)
Begin
SELECT * from user WHERE sex = sexflag;
End
";
mysql_query ($sql);//Create a Myproce4 stored procedure
$sql = "Set @sexflag = 1";
mysql_query ($sql);/set gender parameter to 1
$sql = "Call Test.myproce4 (@sexflag);";
mysql_query ($sql);//Call Myproce4 stored procedure, see effect under CMD


Instance five: Stored procedures that use variables

The code is as follows Copy Code
$sql = "
CREATE PROCEDURE Myproce5 (in a int,in b int)
Begin
declare s int default 0;
Set s=a+b;
Select S;
End
";
mysql_query ($sql);//Create a Myproce5 stored procedure
$sql = "Call Test.myproce5 (4,6);";
mysql_query ($sql);//

Call the myproce5 stored procedure, see the effect under CMD

Example SIX: Case syntax

The code is as follows Copy Code
$sql = "
CREATE PROCEDURE Myproce6 (in score int)
Begin
Case Score
When then select ' Pass ';
When then select ' and good ';
When then select ' Excellent ';
Else select ' Unknown fraction ';
End case;
End
";
mysql_query ($sql);//Create a Myproce6 stored procedure
$sql = "Call Test.myproce6 (100);";
mysql_query ($sql);//

Call the Myproce6 stored procedure, see the effect under CMD

Example Seven: Circular statements

The code is as follows Copy Code
$sql = "
CREATE PROCEDURE Myproce7 ()
Begin
declare i int default 0;
DECLARE j int default 0;
While i<10 do
Set j=j+i;
Set i=i+1;
End while;
Select J;
End
";
mysql_query ($sql);//Create a Myproce7 stored procedure
$sql = "Call Test.myproce7 ();";
mysql_query ($sql);//

Call the Myproce7 stored procedure, see the effect under CMD


Instance eight: Repeat statement

The code is as follows Copy Code
$sql = "
CREATE PROCEDURE Myproce8 ()
Begin
declare i int default 0;
DECLARE j int default 0;
Repeat
Set j=j+i;
Set i=i+1;
Until j>=10
End repeat;
Select J;
End
";
mysql_query ($sql);//Create a Myproce8 stored procedure
$sql = "Call Test.myproce8 ();";
mysql_query ($sql);//

Call the Myproce8 stored procedure, see the effect under CMD

Instance IX: Loop statement

The code is as follows Copy Code

$sql = "
CREATE PROCEDURE Myproce9 ()
Begin
declare i int default 0;
declare s int default 0;

Loop_label:loop
Set s=s+i;
Set i=i+1;
If I>=5 Then
Leave Loop_label;
End If;
End Loop;
Select S;
End
";
mysql_query ($sql);//Create a Myproce9 stored procedure
$sql = "Call Test.myproce9 ();";
mysql_query ($sql);//

Call the Myproce9 stored procedure, see the effect under CMD

Instance ten: Deleting a stored procedure

The code is as follows Copy Code

mysql_query ("drop procedure if exists myproce");//delete test stored procedure

Instance ten: Cursors in Stored procedures

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.