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. — user1467267
– user1467267, Commented Mar 20, 2016 at 19:04
Maybe this might help: stackoverflow.com/questions/14103202/… — user1467267
– user1467267, Commented Mar 20, 2016 at 19:06

Community · Accepted Answer · 2017-05-23 12:31:27Z

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.

Collectives™ on Stack Overflow

PHP repeating SQL queries for each array's item efficiently

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related