php - 使用 Id 显示链接表中的 MySql 数据

Question

我有 2 个表 - 事件和与会者。

我正在尝试使用 php 通过 events.id 单击与会者列表并显示由 id 链接的与会者表中列出的与会者。

涉及的字段是 events.id 和 attendees.id 并显示该事件的参与者。

这就是我目前所拥有的。

SELECT events.*, attendees.* 
FROM attendees ON event.id = attendees.id 
WHERE event.id = attendees.id

Answer 1

您的连接语法是隐式和显式的错误组合 JOIN语法，缺少 JOIN关键字，而是复制 WHERE 中的连接条件条款。

SELECT
  events.*, 
  attendees.* 
FROM
  attendees 
  JOIN events ON event.id = attendees.id
WHERE
  event.id = <event to find attendees for>

请注意，不建议使用 events.*, attendees.*在 PHP 中，因为您将拥有重复的列名，PHP 无法访问这些列名。相反，要明确:

SELECT
  /* Be explicit about the columns you select in a JOIN query */
  events.id AS event_id,
  events.name AS event_name,
  events.someothercol,
  attendees.id AS attendee_id,
  attendees.name AS attendee_name
FROM
  attendees 
  JOIN events ON event.id = attendees.id
WHERE
  event.id = <event to find attendees for>

如果即使没有与会者仍想获取事件详细信息，请使用 LEFT JOIN相反:

SELECT
  /* Be explicit about the columns you select in a JOIN query */
  events.id AS event_id,
  events.name AS event_name,
  events.someothercol,
  attendees.id AS attendee_id,
  attendees.name AS attendee_name
FROM
  events
  /* LEFT JOIN will return event details even when there are no attendees */
  LEFT JOIN attendees ON event.id = attendees.id
WHERE
  event.id = <event to find attendees for>