PHP repeating SQL queries for each array's item efficiently

Question

What's the best way to run SQL query for each item in an array?

I've got the $array of codes:

Array
(
  [0] => 12345
  [1] => 12346
  [3] => 12347
)

Now I'd like to run the following SQL query for each item in the $array:

SELECT * FROM `TABLE` a WHERE a.`Code` = :code

PHP I've been using:

$results = array();

$statement = $pdo->prepare($sql);
$statement->bindParam(':code', $value);

foreach ($array as $key => $value) {
    $statement->execute();

    while (($results = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
        echo $results;
    }
}

When a MySQL cursor is consumed you need to execute the query again. For as far as I know there is no way, besides MySQL caching, to avoid another query. — , Mar 20 '16 at 19:04
Maybe this might help: http://stackoverflow.com/questions/14103202/how-to-fetch-2-times-in-mysql-pdo-without-fetchall — , Mar 20 '16 at 19:06

score 0 · Answer 1 · edited May 23 '17 at 12:31

Rather than running multiple queries, you could run one query and then loop over the results:

SELECT * FROM `TABLE` a WHERE a.`Code` IN (:codes);

Which would be something along these lines in your PHP:

$question_marks = str_repeat("?,", count($array_of_codes)-1) . "?"; 
$statement = $pdo->prepare($sql);
$statement->bindParam(:codes, $question_marks);
$statement->execute($array_of_codes);

while (($results = $statement->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
  echo $results;
}

Where $array_of_codes is your PHP array containing the Code parameter for each result you want to look up.

Credit where it's due This question helped with how to do WHERE...IN queries with PDO.

PHP repeating SQL queries for each array's item efficiently

1 Answers1