SQL join two tables to give multi array result

Question

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.

Now the content of events table is given by

+------------------------------------------------------+
| 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

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

With mysqli you have to sort records by hand, while with PDO it's one line, so the choice is yours. — Your Common Sense
– Your Common Sense, Commented Apr 6, 2016 at 18:00

Alexander Worton · Accepted Answer · 2016-04-07 20:49:42Z

2

Sort the query by category, event name

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>';
    }

}

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.

edited Apr 7, 2016 at 20:49

answered Apr 6, 2016 at 19:07

Alexander Worton

915 bronze badges

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

4 Comments

Anuj TBE Over a year ago

Thank you so much. This is what I wanted. But there is a little issue. The last loop foreach($evets as $event){ echo '<> '.$event.'</br>'; } is printing only <> Array and not the array element

Anuj TBE Over a year ago

I have removed [skills] from second last code and now it's working as expected. Thank you

Anuj TBE Over a year ago

please see the updated question. I had to add one more column event_date to events table. Now how could I show the event date as well with each event in foreach loop. I have added $data[$row['category']][$row['event_date']] = $row['event_date']; just below $row['event_name']

Alexander Worton Over a year ago

You are creating an array of arrays where the 'category' acts as the index for the category grouping and then the event name acts as the index for the array that holds. Rather than add the line you have, add another index to the end of both lines: $data[$row['category']][$row['event_name']]['event_name'] = $row['event_name']; and $data[$row['category']][$row['event_name']]['event_date'] = $row['event_date']; then use the named indexes in your nestedr output loop

Collectives™ on Stack Overflow

SQL join two tables to give multi array result

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related