4

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

Saadia
  • 856
  • 1
  • 12
  • 32

1 Answers1

3

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.
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Community
  • 1
  • 1
Michas
  • 8,534
  • 6
  • 38
  • 62
  • Hi, thanks for have a look. I am assuming by `g.guest_id` you mean `r.guest_id` and by `d.dinner_id` you mean `r.dinner_id` – Saadia May 19 '16 at 16:40
  • thank you so much, i was stuck doing this inside php for over 2 hours, did not know that i had to do was put another `JOIN`. Thanks a million – Saadia May 19 '16 at 16:52