The following code block is a PDO select statement, using try and catch and looping the results out with a while loop. I then prepare a few select queries before the while loop so that I can use rows from the first select statement to loop through and execute the prepared ones with changing variables with each loop. I have found that this works for me up to 3 prepared statements and as soon as I go up to 4 it gives me this error:
General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
I have researched this problem and have tried PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute with no joy. I have tried fetchAll() and that didn't work. I have tried using the $conn->closeCursor(); and that also does not work. I have also tried to unset(); the prepared variable and that also does not work. I have also tried putting the prepared select statements in one query and looping through it using nextRowset() and that also did not work. I have also tried combinations of the above.
So the only way I have got this to work is by putting the prepared variables inside the loop therefore preparing them within each loop. So although this works and without much lag to the response time of the script, it's not ideal. So how do I fix this? Or is the way I fixed it the only way? My hunch is that I need to use a combination of the above listed techniques and I haven't gotten it correct. So in your answers please give a code example showing where to put what. Thank You.
try{
$select_result = $conn->prepare("SELECT * FROM table WHERE id= ?");
$select_result->execute(array($id));
// Prepare Statements //
$second_result = $conn->prepare("SELECT * FROM second_table WHERE id= ? AND status = ?");
$third_result = $conn->prepare("SELECT * FROM third_table WHERE id= ? AND another_field= ? AND status= ?");
$forth_result = $conn->prepare("SELECT * FROM forth_table WHERE id= ? AND another_field= ? AND status= ?");
$fifth_result = $conn->prepare("SELECT * FROM fifth_table WHERE id= ? AND another_field= ? AND status= ?");
$sixth_result = $conn->prepare("SELECT * FROM sixth_table WHERE id= ? AND another_field= ? AND status= ?");
while($select_row = $select_result->fetch())
{
if($select_row['row1'] == 1){
try{
$second_result->execute(array($id, $select_row['field']));
$second_row = $second_result->fetch();
}
catch(PDOException $e){}
}
else{
try{
$third_result->execute(array($id, $select_row['field1'], $select_row['field2']));
$third_row = $third_result->fetch();
}
catch(PDOException $e){}
}
if($select_row['row2'] == 1){
try{
$forth_result->execute(array($id, $select_row['field1'], $select_row['field2']));
$forth_row = $forth_result->fetch();
}
catch(PDOException $e){}
}
if($select_row['row3'] == 1){
try{
$fifth_result->execute(array($id, $select_row['field1'], $select_row['field2']));
$fifth_row = $fifth_result->fetch();
}
catch(PDOException $e){}
}
if($select_row['row4'] == 1){
try{
$sixth_result->execute(array($id, $select_row['field1'], $select_row['field2']));
$sixth_row = $sixth_result->fetch();
}
catch(PDOException $e){}
}
}
}
catch(PDOException $e){}
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire