Saadia Saadia - 6 months ago 10
MySQL Question

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

Scenario

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
r.id as request_id,
r.status as dinner_status,
r.dinner_id,
d.name as dinner_name,
d.date as dinner_date,
u.first_name as user_first_name,
u.last_name as user_last_name,
u.id as user_id,
u.description as user_description,
u.profile_image as user_profile_image,
c.name as user_college_name,
c.id as user_college_id,
c.slug as user_college_slug
FROM `requests` r
LEFT JOIN `college_dinners` d ON r.dinner_id = d.id
LEFT JOIN `user` u ON r.guest_id = u.id
LEFT JOIN `college` c on u.college_id = c.id
WHERE d.college_id = $collegeId
AND u.id = '77'


Now the most important part.

There is a table called
invitations
that keeps the
guest_id
and
dinner_id
ONLY if their request for dinner has been excepted. How can i make use of this
invitations
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
foreach
loop for the results that i get from above query and inside the
foreach
i am creating another query to the table
invitations
and then using
if
i am checking if the current loop's
dinner_id
matches the
dinner_id
and the same for
guest_id
of the second query inside the
foreach
but by doing that i the index gets screwed.

foreach ($invitations as $invitation) {
$invitatedSQL=".....";

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

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.