Joining tables

[jeff5656]

 

I hope this is not confusing. 

Lets say I want to join 2 tables.

 

When I put it through a while loop, how do I write the code so that all id's in table2 are on the SAME line for each referring id in table 1, before dropping to the next line and going to the next record in table 1?

 

I THINK that actually makes sense if you read it carefully :-)

[requinix]

It'll make even more sense if you can post what code you already have.

[jeff5656]

Ok here's kind of the code I would want

$query = "SELECT * FROM items INNER JOIN mainrecords ON items.record_id = mainrecords.id_incr AND items.user_id = '$userid' "; 

$result = mysql_query ($query);
while ($row = mysql_fetch_assoc ($result)) {
  echo echo  items.item1 from all the records in the items table that have record_id equal to mainrecords.id_incr;
"<br>"
} end while

These are the tables:

mainrecord:

id_incr | name

1        | johnson

2          | smith

etc.

 

 

items:

record_id  | item

1              | flashliht

1              | radio

2              | phone

etc.

 

so when I do the while loop, I want all the items associated with the id in the first table to stay on the same line:

mainrecord.id_incr | item1

1                          | flashlight, radio, pocketknife

2                          | phone, paintbrush

[requinix]

GROUP_CONCAT is your friend.

SELECT m.id_incr, GROUP_CONCAT(i.item SEPARATOR ", ") AS items
FROM mainrecords m
JOIN items i ON m.id_incr = i.record_id AND i.user_id = $userid
GROUP BY m.id_incr