PHP准备的stmt问题-在每个结果循环更新数据 [英] php prepared stmt problem- update data at each result loop

问题描述

如果我想通过每个查询循环通过准备好的stmt更新数据，那为什么会失败.错误消息为在进行新的语句准备之前必须获取所有数据"

what if I want to update data via prepared stmt each query loop, but why fails. the error msg is "All data must be fetched before a new statement prepare takes place "

$link = mysqli_connect("localhost", "admin", "", "test");

if (!$link) {
    die('Connect Error: ' . mysqli_connect_error());
}

//field_1 is PK

if ($stmt = mysqli_prepare($link, "SELECT field_1, field_2 FROM table_data")) {
    mysqli_stmt_execute($stmt);    
    mysqli_stmt_bind_result($stmt, $col1, $col2);    

    while (mysqli_stmt_fetch($stmt)) {    
    $updateC= "update table_data set  field_3=? where field_1=?"    

    if ($stmt2= mysqli_prepare($link, $updateC)) {
        mysqli_stmt_execute($stmt2);
        $status='test'; //get return value from function            
        mysqli_stmt_bind_param($stmt2, 'ss', $status, $col1);
    }
  mysqli_stmt_close($stmt2);
 }


    mysqli_stmt_close($stmt);
}


mysqli_close($link);

推荐答案

您正在循环中准备第二条语句，该语句正在从第一条语句中检索数据.您可能可以通过创建两个连接来解决此问题.看到这篇文章:

You are preparing a second statement within a loop which is retrieving data from the first statement. You might be able to solve this by creating two connections. See this post:

http://osdir.com/ml/php.zend.framework.db/2007-07/msg00027.html

您也许还可以将第一个查询的所有行读入一个数组，然后遍历该数组，将第二个查询放入该循环中.

You might also be able to read all the rows from the first query into an array, and then loop over the array, placing your second query in that loop.

可能(我还没有尝试过)，可以提前准备第二条语句.在循环中时，将变量绑定到它并执行它.像这样的东西(完全未经测试！):

It may be possible (and I haven't tried this), to prepare the second statement in advance. When in the loop, bind the variables to it and execute it. Something like this (completely untested!):

$stmt1 = mysqli_prepare($link, "SELECT field_1, field_2 FROM table_data");
$stmt2= mysqli_prepare($link, "update table_data set  field_3=? where field_1=?");

if ($stmt1 && $stmt2) {
    mysqli_stmt_execute($stmt1);    
    mysqli_stmt_bind_result($stmt1, $col1, $col2);

    while (mysqli_stmt_fetch($stmt)) {
        $status='test'; //get return value from function 
        mysqli_stmt_bind_param($stmt2, 'ss', $status, $col1);
        mysqli_stmt_execute($stmt2);
    }

    mysqli_stmt_close($stmt2);
    mysqli_stmt_close($stmt1);
}

这篇关于PHP准备的stmt问题-在每个结果循环更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！