PHP, MySQL prepared statements - can you use results of execute more than once by calling data_seek(
- by Carvell Fenton
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.
