displaying results from several linked tables

best way?

[4string]

I have a main table, 2 other tables and 2 link tables in my db. I'm not sure if I know how to loop through the query result to display everything.

Let's say record id 1 on mainTable has 2 foreign keys linking to table2, and 3 linking to table 3. I end up with rows like:

mainTable(id 1) ¦ table2(id 1) ¦ table3(id 1)
mainTable(id 1) ¦ table2(id 1) ¦ table3(id 2)
mainTable(id 1) ¦ table2(id 1) ¦ table3(id 3)
mainTable(id 1) ¦ table2(id 2) ¦ table3(id 1)
mainTable(id 1) ¦ table2(id 2) ¦ table3(id 2)
mainTable(id 1) ¦ table2(id 2) ¦ table3(id 3)

I presume this is the only way to get all the fields I want. What I'm unsure of is how would I put this into an html table that looks like:

mainTable(id 1)
table2 data:(id 1),(id 2)
table3 data:(id 1),(id 2),(id 3)

I know how to do a loop and get data for each row, but I want to combine the results of related rows. The entries in the db could just return one row or 100+.

I'd appreciate any tips or what to search for on the net. Sorry if I'm not clear enough.

Thanks,
4string

[Spudstr]

you might want to look into using sql JOIN syntax.. this will allow you to join results into one row..

with out seeing the table schema i can't help with much of the syntaxing.. but heres an example

table1 // userID, userName
id
name
1:joe
2:jim

table2 // userID,userLevel
id
level
1:100
2:1

select a.id,a.name,a.level from table1 as a
left join table2 as b on (a.id = b.id)

this will return

a:joe:100
2:jim:1

in your results.. hope that helps.

[4string]

I have the join query ok. The problem lies in the linked tables creating a big matrix of results. Maybe it's too complicated for me to explain without more caffiene. Thanks for trying to help though.