php/mysql userlist and trip list

[c00kie]

I have a database, and within the database i have a table called "users". this contains a line for each user, phone no and all their details. Each user has a unique ID (using auto_increment).

What i would like to do is let people sign up for trips using thier ID. I have a table called trips but im not sure the best way of doing it.

One approach would be to have a line for each trip, then one field for people attending, which would update everytime people join the trip (maybe userids seperated by commas).

Another way would be for each user to have a field called trips, which inserts the trip ID when they sign up for a trip.

At the end of the day i would like to be able to look at a particular trip and see who is coming, and look at a user and see what trips they are/have been signed up to.

I hope this makes sense, i cant really get my head around it so any suggestions welcome!

thanks

 

[c00kie]

hmmm yeh that seems to be the way to do it, thanks!

can you just explain what this does? specificly the "LEFT JOIN" bit

SELECT `users`.*, `events`.* 
  FROM `signups` 
  LEFT JOIN (`users`, `events`) 
    ON (`users`.`userid` = `signups`.`userid` 
      AND `events`.`eventid` = `signups`.`eventid`);

thanks

 

[c00kie]

thats awesome, it seems to work and drags out the trip_id and associated user_id from the db:

$query="SELECT `users`.*, `locations`.* 
  FROM `trips` 
  LEFT JOIN (`users`, `locations`) 
    ON (`users`.`user_id` = `trips`.`user_id` 
      AND `locations`.`location_id` = `trips`.`location_id`)";
	  
$result=mysql_query($query);

mysql_close();

$num=mysql_numrows($result);
$i=0;
while ($i < $num) {

$user_id=mysql_result($result,$i,"user_id");
$trip_id=mysql_result($result,$i,"location_id");

echo "<tr>\n";
echo "<td>".$user_id."</td>\n";
echo "<td>".$trip_id."</td>\n";
echo "</tr>\n";
$i++;
}

I'd like the persons name, and also the location name. I am presuming this must be done using a SELECT * WHERE user_id=$user_id from the previous part, so should it be within the while loop? Is there a more efficient way of doing this?

edit - it seems just by changing user_id to name i could find the name nice