php prepared stmt problem- update data at each result loop

Question

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

Answer 1

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