来自多维数组的Fullcalendar事件数据

Question

I have two tables:

booking_table

+---------+-------+-----+--------+-----------+
| bookID  | start | end | title  | menuId    |
+---------+-------+-----+--------+-----------+
|1        |   xx  | xx  | 1      | 11        |
|2        |   xx  | xx  | 2      | 12        |
|3        |   xx  | xx  | 1      | 13        |
|4        |   xx  | xx  | 3      | 14        |
+---------+-------+-----+--------+-----------+

menu_table

+---------+-------+-----------+-----------+
|     ID  | name  | img       | tMenuId   |
+---------+-------+-----------+-----------+
| 1       |   xx  |  1.jpg    | 11        |
| 2       |   xx  |  2.jpg    | 11        |
| 3       |   xx  |  3.jpg    | 12        |
| 4       |   xx  |  4.jpg    | 12        |
+---------+-------+-----------+-----------+

I'm using fullcalendar.js and codeigniter. I've tried with join tables:

$sql = "SELECT * FROM booking_table JOIN menu_table ON booking_table.menuId = menu_table.tMenuId WHERE booking.start BETWEEN ? AND ? ORDER BY booking.start ASC";

return $this->db->query($sql, array($_GET['start'], $_GET['end']))->result();

but doesn't work as expected. I'm getting :

+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
| bookID  | start | end | title  | menuId    |     ID  | name  | img       | tMenuId   |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
|1        |   xx  | xx  | 1      | 11        | 1       |   xx  |  1.jpg    | 11        |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
|1        |   xx  | xx  | 1      | 11        | 2       |   xx  |  2.jpg    | 11        |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+

I'm getting row 1 from booking_table twice. What I need is:

+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
| bookID  | start | end | title  | menuId    |     ID  | name  | img       | tMenuId   |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
|1        |   xx  | xx  | 1      | 11        | 1       |   xx  |  1.jpg    | 11        |
|         |       |     |        |           | 2       |   xx  |  2.jpg    | 11        |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+
|2        |   xx  | xx  | 2      | 12        | 3       |   xx  |  3.jpg    | 12        |
|         |       |     |        |           | 4       |   xx  |  4.jpg    | 12        |
+---------+-------+-----+--------+-----------+---------+-------+-----------+-----------+ 

I'm populating divs in modal like this:

$('#time').val(data.event ? data.event.start : '');

etc.

My question is:

How can I get fullcalendar events data from multidimensional array?

Answer 1

You can use group_concat to combine the values from multiple rows into one field. Also, If you use aliases on your tables, it can make the query more readable.

$sql = "SELECT 
    b.`bookID`,
    b.`start`,
    b.`end`,
    b.`title`,
    b.`menuId`,
    m.`ID`,
    GROUP_CONCAT(m.`name`) as `names`,
    GROUP_CONCAT(m.`img`) as `images`,
    m.`tMeniId`
FROM booking_table b
JOIN menu_table m
    ON b.menuId = b.tMenuId 
GROUP BY a.bookID, b.menuId
WHERE b.start BETWEEN ? AND ? 
ORDER BY b.start ASC";