Analysis of solutions to the problem of inconsistent return values ​​when calling stored procedures in PHP, inconsistent stored procedures_PHP Tutorial

Analysis of solutions to the problem of inconsistent return values ​​from PHP calls to stored procedures, inconsistent stored procedures

This article describes the solution to the problem of inconsistent return values ​​from PHP calls to stored procedures. Share it with everyone for your reference, the details are as follows:

Today I met a classmate who was talking about the return value of a stored procedure and often got an unexpected value of null. Because I had something to do during the day, I did an experiment in the evening and put it here for students who have corresponding questions to check.

Stored procedure:

delimiter//
createprocedureusp_s2(outpar1int)
begin
selectinet_ntoa(ip),portfromproxy_listlimit5;
selectcount(*)intopar1fromproxy_list;
END//
delimiter;

session 1 execution:

mysql>callusp_s2(@a);
+—————+——+
|inet_ntoa(ip)|port|
+—————+——+
|1.34.21.86  |8088|
|1.34.59.50  |8088|
|1.34.69.15  |8088|
|1.34.73.110  |8088|
|1.34.76.218  |8088|
+—————+——+
5rowsinset(0.00sec)
QueryOK,1rowaffected(0.01sec)
mysql>select@a;
+——+
|@a  |
+——+
|4430|
+——+
1rowinset(0.00sec)

session 2 execution:

mysql>select@a;
+——+
|@a  |
+——+
|NULL|
+——+
1rowinset(0.00sec)

It can be seen that the results obtained by the two sessions are inconsistent. It is basically certain that two calls falling into different sessions will get different values.
For consistency, you can use the following call:

<&#63;php
$hostname="127.0.0.1";
$username="wubx";
$password="wubxwubx";
$database="proxydb";
$db=newmysqli($hostname,$username,$password,$database);
if(mysqli_connect_errno()){
    printf("Connect failed: %s\n",mysqli_connect_error());
    exit();
}
$result=$db->multi_query("call usp_s2(@total); select @total;");
if($result){
    do{
    if($r=$db->store_result()){
        if($r->field_count==2){
            while($row=$r->fetch_row()){
                print"ip: $row[0], port: $row[1]\n";
            }
        }else{
            $row =$r->fetch_row();
            print"total: $row[0]\n";
        }
    }
    }while($db->next_result());
}
$db->close();
&#63;>

$phpt_proc_return.php
ip:1.34.21.86,port:8088
ip:1.34.59.50,port:8088
ip:1.34.69.15,port:8088
ip:1.34.73.110,port:8088
ip:1.34.76.218,port:8088
total:4430

Good luck.

Readers who are interested in more PHP-related content can check out the special topics of this site: "Complete MySQL Stored Procedure Skills", "PHP Data Structure and Algorithm Tutorial", "Summary of PHP Operations and Operator Usage", "PHP Network Programming" Skills Summary", "PHP Basic Syntax Introductory Tutorial", "php Office Document Operation Skills Summary (including word, excel, access, ppt)", "php date and time usage summary", "php object-oriented programming introductory tutorial", "Summary of php string usage", "Introduction to php mysql database operation tutorial" and "Summary of common php database operation skills"

I hope this article will be helpful to everyone in PHP programming.

http://www.bkjia.com/PHPjc/1123829.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1123829.htmlTechArticleAnalysis of solutions to the problem of inconsistent return values ​​when PHP calls stored procedures. Inconsistent stored procedures. This article describes an example of PHP calling stored procedures. Solution to the problem of inconsistent return values. Share to...