Analysis of solutions to the problem of inconsistent return values ​​when calling stored procedures in PHP_php tips

The example in this article describes the solution to the problem of inconsistent return values ​​when calling stored procedures in PHP. 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 on this site: "MySQL stored procedure skills collection", "PHP data structure and algorithm tutorial", "Summary of PHP operations and operator usage", "Summary of PHP network programming skills", "PHP basic syntax introductory tutorial", "PHP operation office document Skills summary (including word, excel, access, ppt) ", "php date and time usage summary ", "php object-oriented programming introductory tutorial ", "Summary of php string (string) usage", "Introduction tutorial on php mysql database operation" and "Summary of common php database operation skills"

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