来自多维数组的完整日历事件数据

Question

我有两个表：

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

我正在使用全日历.js和代码点火器。我尝试过连接表：

$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();

但无法按预期工作。 我得到 ：

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

我两次从booking_table那里得到第 1 行。 我需要的是：

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

我正在像这样在模态中填充div：

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

等。

我的问题是：

如何从多维数组中获取完整的日历事件数据？

Answer 1

您可以使用group_concat将多行中的值合并到一个字段中。此外，如果在表上使用别名，可以使查询更具可读性。

$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";