SQL连接两个表，用多重数组结果 [英] SQL join two tables to give multi array result

问题描述

我有2个表事件和类别。 类别表中有2列 ID 和类别。在事件表中包含 CATEGORY_ID 为表类的外键

I have 2 table events and categories. categories table has 2 columns id and category. The event table contains category_idas foreign key of categories table.

现在事件的内容表由

+------------------------------------------------------+
| id | category_id | event_name | event_date | created |
+------------------------------------------------------+
| 1  |     1       | event 1    | 12-04-2016 |TIMESTAMP|
| 2  |     1       | event 2    | 14-04-2016 |TIMESTAMP|
| 3  |     2       | event 1    | 16-04-2016 |TIMESTAMP|
| 4  |     1       | event 3    | 14-04-2016 |TIMESTAMP|
| 5  |     2       | event 2    | 12-04-2016 |TIMESTAMP|
+------------------------------------------------------+

现在我想要做的是选择所有行和形式产生输出

Now what I want to do is to select all rows and produce output in the form

=> Category 1
     <> event 1
     <><> 12-04-2016
     <> event 2
     <><> 14-04-2016
     <> event 3
     <><> 14-04-2016
=> Category 2
     <> event 1
     <><> 16-04-2016
     <> event 2
     <><> 12-04-2016

如何是可以做到的。我使用 PHP 和的mysqli 我要存储在PHP数组此信息，以便我可以在任何地方打印出来使用PHP页面为环或的foreach

How it can be done. I am using PHP and mysqli and I want to store this information in PHP array so that I could print it anywhere in page using PHP for loop or foreach

推荐答案

按类别查询，事件名称

SELECT  categories.category,
        events.event_name

FROM categories

    LEFT JOIN events
    ON events.category_id = categories.id

ORDER BY categories.category, events.event_name

然后遍历结果来构造要在多维数组：

then iterate through the result to construct the multi-dimensional array you want:

$data = array();

foreach($results as $row){
    $data[$row['category']][$row['event_name']]['event_name'] = $row['event_name'];
    $data[$row['category']][$row['event_name']]['event_date'] = $row['event_date'];
}

您可以再执行一个嵌套的迭代是这样的输出：

you can then perform a nested iteration something like this to output:

foreach($data as $category => $events){

    echo $category.'<br>';

    foreach($events as $event){
        echo '    <> '.$event['event_name'].'<br>';
        echo '        <> '.$event['event_date'].'<br>';
    }

}

而不是使用事件/类别名称为指标的多维数组，你可以抓住事件ID并使用它，如果名称不是唯一的。

Rather than using the event / category names as indexes for the multidimensional array, you could grab the event id and use that instead if the names aren't unique.

这篇关于SQL连接两个表，用多重数组结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！