对具有公共子标题的PHP -2列进行排序

Question

我的代码做了两列，如下所示：

邻域1

餐厅1

邻域1

餐厅2

邻域1

餐厅3

我要：

邻域1

餐厅1

餐厅2

餐厅3

$result = mysql_query("SELECT ID,RName,NHood FROM Restaurants ORDER BY NHood ASC, TRIM(LEADING 'The ' FROM RName) ASC",$connect);
$numRows = mysql_num_rows($result);
$middleIndex = (int)(($numRows+1) / 2);

$names = array();
while($row = mysql_fetch_assoc($result)) {
    $names[] = $row['RName'];
    $id[] = $row['ID'];
    $hood[] = $row['NHood'];
}

// print the left column
echo "<table>";
echo "<tr>";
echo "<td width=60%>";
echo "<div id=\"left\">\n";
for($i = 0; $i < $middleIndex; $i++) {
    echo $hood[$i];
    echo "<p><a href=\"Restaurant.php?ID=".$id[$i]."\">$names[$i]</a></p>\n";
}
echo "</div>\n";
echo "</td>";

// print the right column
echo "<td>";
echo "<div id=\"right\">\n";
for($i = $middleIndex; $i < $numRows; $i++) {
    echo $hood[$i];
        echo "<p><a href=\"Restaurant.php?ID=".$id[$i]."\">$names[$i]</a></p>\n";
}
echo "</div>\n";  
echo "</tr>";
echo "</table>";

Answer 1

下面是您想要的逻辑(pseduocode)：

SELECT
    ID,
    RName,
    NHood
FROM
    Restaurants
ORDER BY
    NHood ASC, RName ASC

这应该返回一个结果的列表，首先由邻居，然后根据餐馆的名称。

接下来，您将逐行处理这些结果，以创建具有视图所需数据结构的嵌套数组：

$neighborhoods = array();

foreach($result as $row)
{
    // Add each restaurant to a list named for the current neighborhood
    $neighborhoods[$row['NHood']][] = $row['RName'];
}

这将产生这样的数据结构：

$neighborhoods = array(
    'neighborhood1' => array(
        'restaurant1',
        'restaurant2',
        'restaurant3',
    ),
    'neighborhood2' => array(
        'restaurant1',
        'restaurant2',
    ),
)

然后，您将通过这样做来使用：

foreach($neighborhoods as $neighborhood => $restaurants)
{
    echo $neighborhood;
    foreach($restaurants as $restaurant)
    {
        echo $restaurant;
    }
}

如果您的列表非常大，并且您担心内存，那么实现就会有点不同，但是逻辑是一样的。这种技术更快，但是读取和维护起来有点困难，因为表示和数据层逻辑都是混合在一起的：

$currentNH = null;

while($row = fetch_row($results))
{
    // Detect when the neighborhood changes
    if($currentNH != $row['NHood']) {
        echo $row['NHood'];
    }

    echo $row['RName'];
}