Analysis of solutions to the problem of inconsistent return values when calling stored procedures in PHP

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

WBOY

Release： 2016-05-16 19:53:42

Original

1235 people have browsed it

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;

Copy after login

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)

Copy after login

session 2 execution:

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

Copy after login

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

Copy after login

$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"