[SOLVED] simple join

[Stooney]

Not too familiar with table joins yet.  I'll keep it short.

 

I have a table called events_signup.  I pull all of the event_id's from this table where user_id=the user.  Easy, simple query.

 

Now I need to query the events table to pull the event information for each of the event id's I just got.  I'm not sure how to go about this without a separate query for each event id.  Not even sure what keywords to google.  Any help is appreciated.

[AP81]

SELECT events_signup.EVENT_ID, events.EVENT_NAME
FROM events_signup LEFT JOIN events ON events.EVENT_ID = events_signup.EVENT_ID
WHERE USER_ID = 1

 

This assumes that the events table has a column called EVENT_NAME, so replace the columns with the one you need.  This will include all events, even if they don't have an event name because it is a LEFT JOIN.

 

You could also do this instead, which will only include all events that have an event name.

 

SELECT events_signup.EVENT_ID, events.EVENT_NAME
FROM events_signup, events
events.EVENT_ID = events_signup.EVENT_ID
AND USER_ID = 1

[Stooney]

That looks close to what I'm looking for.  I will explain better this time. 

 

Here is the 'long' way of doing what I need.  This is with all the real variable/table names/fields.  This will return an array of resource, but I need it all in a single resource.

 

<?php
//Grabs all the events the user is signed up for
$event_temp=Dbc::query("SELECT event_id FROM ".DB_PREFIX."_events_signup WHERE user_id='$user_id'");

//Grabs all the info for each event the user is signed up for
while($row=Dbc::fetch($event_temp)){
$events[]=Dbc::query("SELECT * FROM ".DB_PREFIX."_events WHERE date>NOW() AND event_id='$row[event_id]'");
}
?>

[AP81]

Try this:

 

SELECT events_signup.event_id, events.*
FROM events_signup, events
WHERE events_signup.event_id = events.event_id 
AND user_id='$user_id'
AND events.date > NOW()

 

 

[Stooney]

TY sir. 

 

Final Solution:

Dbc::query("SELECT ".DB_PREFIX."_events_signup.event_id, ".DB_PREFIX."_events.* FROM ".DB_PREFIX."_events_signup, ".DB_PREFIX."_events WHERE ".DB_PREFIX."_events_signup.event_id = ".DB_PREFIX."_events.event_id  AND user_id='$user_id' AND ".DB_PREFIX."_events.date > NOW()");