Joining multiple tables.

[pthurmond]

Ok I have a problem with getting data from 3 tables at the same time. I want to do a triple join, but one of the tables will need to return multiple entries which I then want to build a list of activity names that are separated by commas.

Currently I have the following...
[code]
$query = "SELECT p.First_Name, p.Last_Name, e.Date, e.Signin_Time, FROM Participants AS p, Event_Log AS e WHERE p.Participant_ID = e.Participant_ID AND e.Date >= '$d1' AND e.Date <= '$d2' AND e.Center_ID = '$center' ";
[/code]

I have a third table that has multiple entries associated with this Log entries ID number, each row containing a different activity ID number. This number references an activity and its name from a fourth table.
For each log record that I pull from the database I need to compile a string of activities (their names) separated by commas. And I have to list it in the output for each row before going to the next one.
Any suggestions. The only idea I have that might work is building a 2-dimensional array of all the rows to output with the first column containing the output string and the second containing the Event Log ID. Then taking this array and doing another query that will return the name of each Activity associated with that Event Log ID and using a loop to build a string of the activities names and then replace the ID in the array with the created string. Finally outputting the entire array after replacing all the values.

This idea of mine is going to be very complex (lots of typing), so if anyone knows how to use a join to make this simpler or some other way to do this, please let me know.

Thanks,
Patrick

[garry27]

i'm not sure what you mean but i always use where clauses to join tables. it uses an inner join by default and is much simpler than using mysql JOINS clauses. ie

select * from a,b,c
where key.a=key.b
and key.b=key.c

i think there's an example of this at www.w3schools.com

[roopurt18]

I was able to infer the following from your SQL query about your table structure:

[b]Participants[/b]
Participant_ID
First_Name
Last_Name


[b]Event_Log[/b]
Participant_ID
Date
Signin_Time

Why don't you give the basic format of the other two tables you're talking about and then we might be able to give you a better answer.

[pthurmond]

Actually I found a somewhat elegant solution to the problem.
Basically what I did is pull up each log record and store the first 75% of the html row into a 2-dimensional array. That html string was stored in the first column of the array and the record id is stored in the second column.
I then did a second query that joined two other tables together to get the names of each activity entry found and creates a string of activity names separated by commas.
I then surrounded that string in the appropriate html and appended it on to the end of the string in the first column of the array.
Once I did this for every row in the array I then used a foreach loop to echo every row to the screen.

This is pretty much the idea I was talking about when I first asked the question and after looking at it I realized that this was the best solution. Gotta love arrays.

Anyways, thanks for helping.

Thanks,
Patrick