Help with query/output for one to many.

[sledge4]

I have two tables, for which I want to output data from.  Here's an example of what I am trying to accomplish.  The ID's link/join each table.

 

Table 1 (ID | Name)

1 | John

2 | Bob

3 | Sara

 

Table 2 (ID | Type)

1 | Soda

1 | Candy

2 | Beer

2 | Ice Cream

3 | Popcorn

 

I'm trying to output the data like this:

 

John - Soda, Candy

Bob - Beer, Ice Cream

Sara - Popcorn

 

Instead I get this

 

John - Soda

John - Soda

Bob - Beer

Bob - Ice Cream

Sara - Popcorn

 

Any point in the right direction would be GREATLY appreciated.

 

Thanks!

[Maq]

Post your current query...

[sledge4]

SELECT table1.name, table2.stable FROM table1 INNER JOIN table2 ON table1.id = table2.id ORDER BY table1.name ASC, table1.id ASC

 

Thanks!

[fenway]

Try using GROUP_CONCAT().

[sledge4]

Ok, group_concat worked...thanks!  But I have another question.  Is it possible to link each result?

 

Here's my code:

 

<?
$query = mysql_query("SELECT a_site_residents.name AS Resident, GROUP_CONCAT(a_site_stables.stable ORDER BY a_site_stables.stable ASC SEPARATOR ', ') AS Stable FROM a_site_residents, a_site_stables WHERE a_site_residents.id=a_site_stables.id GROUP BY a_site_residents.name");
$numrows=@mysql_num_rows($query);
if($numrows != 0) {
while ($result = mysql_fetch_array($query)) {
?>

<table border="1" width="100%">
<tr>
	<td width="100" valign="top"><? echo $result['Resident']; ?></td>
	<td><? echo $result['Stable']; ?></td>
</tr>
</table>

<? } } ?>

 

 

What I'm wondering if I can do is link each "Stable" result that is being output.

 

Thanks!

[fenway]

What doyou mean "likn to each".

[sledge4]

Place an <a> around each of the results.

 

Example...

 

John | <a>Popcorn</a>,  <a>crackerjacks</a>, <a>Beer</a>

 

 

[fenway]

That has nothing to do with mysql.