Saadia Saadia - 1 year ago 45
MySQL Question

PHP - Not able to link tables to get the required information


A user can show an interest to attend an event's dinner and the people who administer that event can choose to accept their request or not and if the request has already been accepted the "Accept" button should then be disabled.

Whats working

Everything is working till the part where the button needs to be disabled, the information is displaying right and i am having hard time linking database tables up so i can then use the information i need to disable the button.

The Attempt

This is what my query looks like

SELECT as request_id,
r.status as dinner_status,
r.dinner_id, as dinner_name, as dinner_date,
u.first_name as user_first_name,
u.last_name as user_last_name, as user_id,
u.description as user_description,
u.profile_image as user_profile_image, as user_college_name, as user_college_id,
c.slug as user_college_slug
FROM `requests` r
LEFT JOIN `college_dinners` d ON r.dinner_id =
LEFT JOIN `user` u ON r.guest_id =
LEFT JOIN `college` c on u.college_id =
WHERE d.college_id = $collegeId
AND = '77'

Now the most important part.

There is a table called
that keeps the
ONLY if their request for dinner has been excepted. How can i make use of this
table to update my query and include information in it based on which i can enable and disable button on my view?

The PHP Attempt

I am open to solving this in PHP it self too, so I decided the use the
loop for the results that i get from above query and inside the
i am creating another query to the table
and then using
i am checking if the current loop's
matches the
and the same for
of the second query inside the
but by doing that i the index gets screwed.

foreach ($invitations as $invitation) {

if ( $invitedResult['guest_id'] == $invitation['user_id'] && $invitedResult['dinner_id'] == $invitation['dinner_id'] ) {
$isInvited = 'yes';

Please note that I do not need any help on the front end, its just the mysql query that I am having the problem with. I will really appreciate any help on this matter

Answer Source

Just a simple, left join. If the condition is false, there will be NULLs.

LEFT JOIN `invitations` i on r.guest_id = i.guest_id AND r.dinner_id = i.dinner_id

P.S. Few of Your LEFT JOINs acts like a plain INNER JOINs. You should read about differences.