PHP替代在循环内使用查询 [英] 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.

我有一个数组，例如

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

这些只是3个索引.我正在使用的数组没有恒定的大小，因此有时它可以包含多达20个索引.

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. 用于循环
2. 查询服务器并选择居住在洛杉矶"的所有人的名字
3. 打印出姓名

输出将如下所示:

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?

推荐答案

如果从数组开始，则进一步添加到MrCodes答案中:-

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 />";
    }
}

作为数据库设计问题，您可能应该将城镇名称放在单独的表中，并且该人的表包含城镇的ID，而不是实际的城镇名称(使验证更加容易，快捷并且验证的可能性较小因为有人打错了家乡而错过记录)

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)

这篇关于PHP替代在循环内使用查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！