Hey, I'm running a MySQL query on a page of my website but I want to exclude some values from appearing if a user has already responded to the invitation therefore I run another query but I'm not sure how to combine them. Here's some of my code and my querys. <?php //connect to db here //I've set the country to uk here to make the code easier to look through. in the script it finds the users country from their IP address. $country = "uk"; //this shows all the responses made by users $responses = mysql_query("SELECT * FROM responses WHERE userid='$id'"); //this shows all the events that match the users country $events = mysql_query("SELECT * FROM events WHERE country='$country'"); ?> PHP: What I want to do is somehow combine these querys so that if the user has already responded to an event (i.e. a record exists in the "responses" table with the corresponding eventid) then I don't want to show that result. E.g. There are 5 events for the UK. If John has responded to events 1, 2 and 3 the only events that will show up are eventid 4 and 5.
This is going to require a LEFT JOIN on a subquery, its a little complex, but possible. Your main query will be from the events table, then you will LEFT JOIN that to a subquery that pulls all the events your user has responded to (a simple JOIN between the responses and events tables), finally in the main query you will limit your results to just those events that have a null value returned from the subquery--meaning there is no response to that event from your user.
Hey Plog, Thanks for responding. Would you be able to give me an example? - I've been looking on Google but I have had no success at all - forgive me I'm still pretty new to MySQL/PHP.
Below is a query that would work, assuming these things: 1. The events table has a unique id number field called 'eventid'. 2. The response table has a field called 'idevent' which holds 'eventid' values from the events table. 3. The response table has a field called 'iduser' which holds values the unique id number of users (presumably from a user table). 4. The events table has field called eventname, eventlocation. SELECT tempquery.idevent, events.eventid, events.eventname, events.eventlocation FROM events LEFT JOIN (SELECT responses.idevent, responses.iduser FROM responses WHERE (((responses.iduser)=3)) ) AS tempquery ON events.eventid=tempquery.idevent WHERE (((tempquery.idevent) Is Null) AND ((events.eventlocation)="uk")); PHP: Also, this just pulls results for user 3 for events in 'uk'. To make this dynamic you would have to drop in your php variables in the WHERE clause above where those values appear. If you have any problems getting this to work, post the structure of your events and responses tables.