mysql - PHP Alternative to using a query within a loop


I was told that it is a bad practice to use a query (select) within a loop because it slows down server performance.

I have an array such as

Array ( [1] => Los Angeles )
Array ( [2] =>New York)
Array ( [3] => Chicago )

These are just 3 indexes. The array I'm using does not have a constant size, so sometimes it can contain as many as 20 indexes.

Right now, what I'm doing is (this is not all of the code, but the basic idea)

  1. For loop
  2. query the server and select all people's names who live in "Los Angeles"
  3. Print the names out

Output will look like this:

Los Angeles
      Michael Stern
      David Bloomer
      William Rod

New York
      Kary Mills

      Henry Davidson
      Ellie Spears

I know that's a really inefficient method because it could be a lot of queries as the table gets larger later on.

So my question is, is there a better, more efficient way to SELECT information based on the stuff inside an array that can be whatever size?




Use anIN query, which will grab all of the results in a single query:

SELECT * FROM people WHERE town IN('LA', 'London', 'Paris')



To further add to MrCodes answer, if you start with an array:-

$Cities = array(1=>'Los Angeles', 2=>'New York', 3=>'Chicago');
$query = "SELECT town, personname FROM people WHERE town IN('".implode("','", $Cities)."') ORDER BY town";
if ($sql = $mysqliconnection->prepare($query)) 
    $result = $sql->get_result();
    $PrevCity = '';
    while ($row = $result->fetch_assoc()) 
        if ($row['town'] != $PrevCity)
            echo $row['town']."<br />";
            $PrevCity = $row['town'];
        echo $row['personname']."<br />";

As a database design issue, you probably should have the town names in a separate table and the table for the person contains the id of the town rather than the actual town name (makes validation easier, faster and with the validation less likely to miss records because someone has mistyped their home town)




That's the purpose of prepared statements. You bind a placeholder to a value, and use it like a variable, with the same query. Since the query hasn't changed, you minimize the communication with the mysql server, resulting in an efficiency boost.

Example using PDO:

$cities = array(
    "Los Angeles",
    "New York",

try {
    //Change database parameters here (user, pass, database name)
    $db = new PDO("mysql:host=localhost;dbname=users", "user", "pass");
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $stmt = $db->prepare("SELECT * FROM `users` WHERE `city` = :city");

    foreach ($cities as $city) {
        $stmt->bindValue(":city", $city);

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        //Output data here. You can format it however you'd like
        var_dump($city, $result);

catch (PDOException $e) {
    //Error handling here

People are also looking for solutions to the problem: mysql - PHP function to compare data in two tables to incorporate in existing script


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.