php - 按类别对项目进行排序

Question

我有一个包含以下记录的表:

product_id | title   | category
------------------------------------
1          | apple   | mobile
2          | android | mobile
3          | dell    | desktop
4          | hp      | desktop

以及以下查询:

$sql = "SELECT product_id, title, category FROM products ORDER BY category"; 
$stmt = $db->prepare($sql); 
$stmt->execute(); 
  while($results = $stmt->fetch(PDO::FETCH_ASSOC)) 
{ 
echo $results["product_id"];
echo $results["title"];
echo $results["category"];
}

问题是如何拆分结果并在按类别排序的列表中显示所有记录，如下所示:

• 移动
• 苹果
• 安卓

桌面

• 戴尔
• 惠普

Answer 1

获取结果集后对记录进行分组:

$products = array();

while ($results = $stmt->fetch(PDO::FETCH_ASSOC)) 
{ 
    $products[$results['category']][] = array(
        'product_id' => $results['product_id'],
        'title'      => $results['title']
    );
}

foreach ($products as $category => $productList)
{
    echo $category . '<br>';

    foreach ($productList as $product)
    {
        echo $product['title'] . '<br>';
    }

    echo '<hr />';
}