PHP, MySQL prepared statements - can you use results of execute more than once by calling data_seek(0)?

Question

Hello,

I have a case where I want to use the results of a prepared statement more than once in a nested loop. The outer loop processes the results of another query, and the inner loop is the results of the prepared statement query. So the code would be something like this (just "pseudoish" to demonstrate the concept):

// not showing the outer query, it is just a basic SELECT, not prepared statement
// we'll call it $outer_query

$obj_array = array();   // going to save objects in this
$ids = array(18,19,20); // just example id numbers

$query = "SELECT field1, field2 FROM table1 WHERE id=?";
$stmt = $db->prepare($query);

foreach ($ids as $id) {
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->bind_result($var1, $var2);
    $stmt->store_result(); // I think I need this for data_seek

    while ($q1 = $outer_query->fetch_object()) {
        while ($stmt->fetch()) {
            if ($q1->field1 == $var1) { // looking for a match
               $obj = new stdClass();
               $obj->var1 = $var1;
               $obj->var2 = $var2;

               $obj_array[] = $obj;
               $stmt->data_seek(0); // reset for outer loop
               break;               // found match, so leave inner                
            }
        }
    }
}

The problem I seem to be experiencing is that the values are not getting bound in the variables as I would expect after the first time I use fetch in the inner loop. Specifically, in one example I ran with 3 ids for the foreach, the first id was processed correctly, the second was processed incorrectly (matches were not found in the inner loop even though they existed), and then the third was processed correctly.

Is there something wrong with the prepared statment function calls in the sequence I am doing above, or is this an invalid way to use the results of the prepared statement?

Thanks.

Answer 1

You'd better use JOIN to make it with single query and no loops

And you don't need any data_seek anyway.

Answer 2

Convert the mysql result to an array, and then you can use array functions to navigate through the array. Data_seek isn't very elegant, IMO.

Answer 3

I found where this code was going wrong. It had nothing to do with my use of the prepared statement functions, just the placement of the data_seek. I sought help from SO a little too soon :)

The loop code that now works exactly as I wanted is as follows:

while ($q1 = $outer_query->fetch_object()) {
    while ($stmt->fetch()) {
        if ($q1->field1 == $var1) { // looking for a match
            $obj = new stdClass();
            $obj->var1 = $var1;
            $obj->var2 = $var2;
            $obj_array[] = $obj;
            break;               // found match, so leave inner                
        }
    }
    $stmt->data_seek(0); // reset for outer loop
}
// this was always here, forgot it in original post
$outer_query->data_seek(0) // reset for next iteration of foreach

When I was calling data_seek inside the conditional to reset the prepared statment results, the reset was occurring only if a match was found. This caused the outer loop to run in specific situations with the prepared statement result not reset. Sometimes the forest is hidden by the trees ;)

So, apparently you can easily process prepared statement results multiple times using data_seek, just as I had hoped.

Thanks.