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

 

[Fr0dders]

you need to get the tables to relate

im unsure of how mysql works, but this is how i'd do it in MS SQL Server

You'd need to set the User_ID to be a primary key. This specifies it must not be a NULL value, and must be unique

You then do the same thing with Trip ID so that easy trip is unique. You then in your "Trips" table add a User_ID Colum. You make this a foreign key. This means that is must therefore have a value that exists in a table elsewhere. You then relate the foreign key User_ID in trips to Primary Key User_ID in Users

for multiple IDs, id add User_ID2, User_ID3 etc.. for how many people can attend the trip. I believe you can have multiple foreign keys to a single primary.

this can be done in enterprise manager on MS SQL, so google for how to do it in mysql

 

[Dj_Jestar]

Create a 3rd table for signups, have 3 columns:

Signup ID (aka order number), event ID (foreign key of the even they are signing up for..) and user id (foreign key of the user)

Then:

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

 

[LazyManc]

You'd probably be better off with a 3rd associative table in this situation, so user, trip and an "trip_attendee" table

user table would be
user_id, name, etc

trip table would be
trip_id, destination, etc

and trip_attendee table would just be
trip_id, user_id

So each trip would have 1 row in the trip table, and then for each user on the trip, you'd have a row in the attendee table.

 

[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

 

[Dj_Jestar]

a left join returns everything from the table on the left of the "JOIN", that matches the criteria, whilst returning everything from the table on the right.

so with "ON `tableA`.`column` = `tableB`.`column`" would return only those rows on tableA that have the field `column` matching that of the field `column` on tableB.

An inner (aka cross) join would return records from both tables, that only match each other.

example:

TableA
col1
 2
 4
 6
 8

TableB
col1
 1
 2
 3
 4
 5
 6
 7
 8

SELECT `tableA`.*, `tableB`.* 
FROM `tableA` 
  LEFT JOIN `tableB` 
    ON `tableA`.`col1` = `tableB`.`col1`;

Will return:

Results:
TableA_col1 TableB_col1
		1
	2	2
		3
	4	4
		5
	6	6
		7
	8	8

now let's say we delete record 4 from TableB.col1..

Results:
TableA_col1 TableB_col1
		1
	2	2
		3
		5
	6	6
		7
	8	8

would be the results.

And if we didn't want the information from tableB (i.e. the SELECT changes to "SELECT `tableA`.`col1` FROM blah blah" but we still use the same JOIN,) we would get:

Results:
TableA_col1
	2	
	6	
	8

 

[Fr0dders]

edit

Jester has explained it much better

 

[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