Viewing MySql data from linked arrays using Id
source link: https://www.codesd.com/item/viewing-mysql-data-from-linked-arrays-using-id.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Viewing MySql data from linked arrays using Id
I have 2 tables - events and attendees.
I am trying using php to when clicking on the attendee's list via the events.id and to display the attendees listed in the attendees table linked by id.
fields envolved are events.id and attendees.id and to display the attendees for that event.
This is what I have at the moment.
SELECT events.*, attendees.*
FROM attendees ON event.id = attendees.id
WHERE event.id = attendees.id
Your joining syntax is an incorrect mix of implicit and explicit JOIN
syntaxes, missing the JOIN
keyword, and instead duplicating the join condition in the WHERE
clause.
SELECT
events.*,
attendees.*
FROM
attendees
JOIN events ON event.id = attendees.id
WHERE
event.id = <event to find attendees for>
Note that it is not advisable to use events.*, attendees.*
in PHP, since you will have duplicate column names that become inaccessible to PHP. Instead, be explicit:
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>
If you still want to get the event details even if it has no attendees, use a LEFT JOIN
instead:
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>
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK