PHP Alternative to using a query within a loop

Question

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)

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

Output will look like this:

Los Angeles
      Michael Stern
      David Bloomer
      William Rod

New York
      Kary Mills

Chicago
      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?

For anyone using mysqli prepared statements with a variable number of placeholders, here is a usable snippet: stackoverflow.com/a/56714257/2943403 (granted PDO makes this task easier) — mickmackusa
– mickmackusa ♦, Commented Jun 22, 2019 at 11:56

MrCode · Accepted Answer · 2012-11-30 10:23:54Z

5

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

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

answered Nov 30, 2012 at 10:23

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

And how does one then output the results in the desired fashion? This answer completely fails to address the question the OP asked.

MrCode Over a year ago

@eggyal the real question is how to improve the efficiency of the repeated queries, which I answered. OP never actually asked how to output it in that way. If OP has specific problems with the output then I would gladly help.

Kickstart · Accepted Answer · 2012-11-30 10:43:36Z

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)) 
{
    $sql->execute();
    $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)

Community · Accepted Answer · 2017-05-23 10:28:35Z

As @MrCode mentions, you can use MySQL's IN() operator to fetch records for all of the desired cities in one go, but if you then sort your results primarily by city you can loop over the resultset keeping track of the last city seen and outputting the new city when it is first encountered.

Using PDO, together with MySQL's FIELD() function to ensure that the resultset is in the same order as your original array (if you don't care about that, you could simply do ORDER BY city, which would be a lot more efficient, especially with a suitable index on the city column):

$arr = ['Los Angeles', 'New York', 'Chicago'];
$placeholders = rtrim(str_repeat('?, ', count($arr)), ', ');

$dbh = new PDO("mysql:dbname=$dbname", $username, $password);
$qry = $dbh->prepare("
  SELECT   city, name
  FROM     my_table
  WHERE    city IN ($placeholders)
  ORDER BY FIELD(city, $placeholders)
");

if ($qry->execute(array_merge($arr, $arr))) {

  // output headers
  echo '<ul>';

  $row = $qry->fetch();
  while ($row) {
    $current_city = $row['city'];

    // output $current_city initialisation
    echo '<li>'.htmlentities($current_city).'</li><ul>';
    do {
      // output name $row
      echo '<li>'.htmlentities($row['name']).'</li>';
    } while ($row = $qry->fetch() and $row['city'] == $current_city);
    // output $current_city termination
    echo '</ul>';
  }

  // output footers
  echo '</ul>';
}

Madara's Ghost · Accepted Answer · 2012-11-30 10:23:30Z

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",
    "Chicago"
);

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

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

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

        $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
}

Collectives™ on Stack Overflow

PHP Alternative to using a query within a loop

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Linked

Related