Detailed explanation of how PHP calls MySQL stored procedures

Home

Backend Development

PHP Tutorial

Detailed explanation of how PHP calls MySQL stored procedures_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 13, 2016 pm 05:06 PM

mysql php under and introduce exist sky storage method yes Detailed explanation transfer process

php and mysql are a natural pair. Let me introduce how to call mysql stored procedures in php and execute the returned results. Below I have summarized some commonly used methods of calling MySQL stored procedures in php. Students who need to know more can refer to.

MySQL has only introduced stored procedures since 5.0. Anyway, I have never touched them before when I was working on applications. However, now because it is mainly used for internal systems, many applications use stored procedures. Of course, the front desk sometimes needs to call MySQL stored procedures. , PHP’s MySQL Lib doesn’t seem to support it very well, but I searched for some information. Although there is not much, I still tried to use it. Now I will introduce the method

1. Methods that call stored procedures.

a. If the stored procedure has IN/INOUT parameters, declare a variable and input the parameters to the stored procedure. The variable is a pair, a PHP variable (it is not necessary, but there is no way to perform dynamic input when there is no PHP variable) and a Mysql variable.

b. If the stored procedure has an OUT variable, declare a Mysql variable. The declaration of mysql variables is special. The mysql server must know the existence of this variable. In fact, it means executing a mysql statement. Enter 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】");

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

In this way, there is a variable in the mysql server, @mysqlar. If it is an IN parameter, its value can be passed in from phpar.

d. If stored procedure.

1. Execute the call procedure() statement.

That is, mysql_query("call procedure([var1]...)");

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

代码如下	复制代码
mysql_query("select @var)"

The next operation is the same as php executing a general mysql statement. The results can be obtained through functions such as mydql_fetch_row().

Below I have summarized some examples of calling stored procedures. Stored procedures without parameters

代码如下

复制代码

$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
mysql_query($sql);//创建一个myproce的存储过程

$sql = "call test.myproce();";
mysql_query($sql);//调用myproce的存储过程，则数据库中将增加一条新记录。

The code is as follows

Copy code

$conn = mysql_connect('localhost','root','root') or die ("Data connection error!!!");

代码如下

复制代码

$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; //输出来自存储过程中输出的变量

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 the stored procedure of myproce, and a new record will be added to the database.

Type 1: Calling a method 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 the variables output from the stored procedure

类型二：调用带多个输出类型和多个输入类型参数的方法

代码如下

复制代码

代码如下

复制代码

$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.'
'; //输出来自存储过程中输出的变量
echo 'User id:'.$uId; //获取用户id

类型三：调用带返回结果集的方法

代码如下

复制代码

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']; //输出数据集
}

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

代码如下

复制代码

代码如下

复制代码

//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 ……
……

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

类型四：调用带返回多个结果集的方法(目前只能通过mysqli来实现~~)

代码如下

复制代码

Example 4: inout stored procedure of outgoing parameters

代码如下	复制代码
$sql = " create procedure myproce4(inout sexflag int) begin SELECT * FROM user WHERE sex = sexflag; end; "; mysql_query($sql);//创建一个myproce4的存储过程 $sql = "set @sexflag = 1"; mysql_query($sql);//设置性别参数为1 $sql = "call test.myproce4(@sexflag);"; mysql_query($sql);//调用myproce4的存储过程,在cmd下面看效果

Example 5: Stored procedure using variables

The code is as follows

Copy code

$sql = "

代码如下	复制代码
$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);//创建一个myproce5的存储过程 $sql = "call test.myproce5(4,6);"; mysql_query($sql);//

create procedure myproce5(in a int,in b int)

begin

declare s int default 0;

set s=a+b;

select s;

代码如下	复制代码
$sql = " create procedure myproce6(in score int) begin case score when 60 then select '及格'; when 80 then select '及良好'; when 100 then select '优秀'; else select '未知分数'; end case; end; "; mysql_query($sql);//创建一个myproce6的存储过程 $sql = "call test.myproce6(100);"; mysql_query($sql);//

end;

mysql_query($sql);//Create a myproce5 stored procedure

$sql = "call test.myproce5(4,6);";

mysql_query($sql);//

代码如下	复制代码
$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);//创建一个myproce7的存储过程 $sql = "call test.myproce7();"; mysql_query($sql);//

Call the stored procedure of myproce5 and see the effect under cmd

Example 6: case syntax

The code is as follows

Copy code

$sql = "
create procedure myproce6(in score int)

begin

代码如下	复制代码
$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);//创建一个myproce8的存储过程 $sql = "call test.myproce8();"; mysql_query($sql);//

case score when 60 then select 'pass'; when 80 then select '和好'; when 100 then select 'Excellent'; else select 'Unknown score'; end case; end; "; mysql_query($sql);//Create a myproce6 stored procedure $sql = "call test.myproce6(100);"; mysql_query($sql);// Call the stored procedure of myproce6 and see the effect under cmd Example 7: Loop statement

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 stored procedure of myproce7 and see the effect under cmd<🎜> <🎜><🎜> Example 8: 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 stored procedure of myproce8 and see the effect under cmd

Example 9: loop statement

The code is as follows

代码如下

复制代码

$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);//创建一个myproce9的存储过程
$sql = "call test.myproce9();";
mysql_query($sql);//

Copy code

$sql = "
create procedure myproce9()
begin
declare i int default 0;
declare s int default 0;

代码如下	复制代码
mysql_query("drop procedure if exists myproce");//删除test的存储过程

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 stored procedure of myproce9 and see the effect under cmd

Example 10: Delete stored procedure

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Best Graphic Settings

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Seashell Riddle Solution

2 weeks ago By DDD

R.E.P.O. How to Fix Audio if You Can't Hear Anyone

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: How To Unlock Everything In MyRise

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7495

CakePHP Tutorial

1377

What is the format of the account name of steam

win11 activation key permanent

nyt connections hints and answers

Related knowledge

The Future of PHP: Adaptations and Innovations Apr 11, 2025 am 12:01 AM

The future of PHP will be achieved by adapting to new technology trends and introducing innovative features: 1) Adapting to cloud computing, containerization and microservice architectures, supporting Docker and Kubernetes; 2) introducing JIT compilers and enumeration types to improve performance and data processing efficiency; 3) Continuously optimize performance and promote best practices.

How to create navicat premium Apr 09, 2025 am 07:09 AM

Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.

MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

PHP vs. Python: Understanding the Differences Apr 11, 2025 am 12:15 AM

PHP and Python each have their own advantages, and the choice should be based on project requirements. 1.PHP is suitable for web development, with simple syntax and high execution efficiency. 2. Python is suitable for data science and machine learning, with concise syntax and rich libraries.

MySQL and SQL: Essential Skills for Developers Apr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

How to create a new connection to mysql in navicat Apr 09, 2025 am 07:21 AM

You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.

How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

PHP: Is It Dying or Simply Adapting? Apr 11, 2025 am 12:13 AM

PHP is not dying, but constantly adapting and evolving. 1) PHP has undergone multiple version iterations since 1994 to adapt to new technology trends. 2) It is currently widely used in e-commerce, content management systems and other fields. 3) PHP8 introduces JIT compiler and other functions to improve performance and modernization. 4) Use OPcache and follow PSR-12 standards to optimize performance and code quality.

See all articles