Output single row from table A once, while including multiple rows from table B

[pepsi_max2k]

Hey all, I've got a mysql/php question about database normalization. I've got the following tables:

 

Products - inc. product ID and data.

Publishers - inc. publisher ID and data.

Products/Publishers - inc. product ID and publisher ID, can be multiple entries for each product.

 

What code would I need to grab and output just a single product from the products table, but include all publishers for that product?

 

I can already do the output from a single row by using "$num=mysql_numrows($result);" and a while loop for $num, but as soon as I start joining tables that have multiple entries in a secondary table, it'll then decide there's X rows and output all data from the first table X times, and just change the publisher each time.

 

Thanks, pepsi_max2k.

[pepsi_max2k]

Oh hang on...

 

I'm doing:

 

$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$publisher=mysql_result($result,$i,"publisher");
echo $name;
echo $publisher;
$i++; }

 

Which would output twice for one product with two publishers. But if I only have one product...

 

 

$name=mysql_result($result,$i,"name");
echo $name;

$i=0;
while ($i < $num) {
$publisher=mysql_result($result,$i,"publisher");
echo $publisher;
$i++; }

 

One product output, multiple publishers, correct? Silly me...

 

 

 

Except... What if I have one product, two publishers, and three... say colours (in a seperate linked table). How do I loop through the publishers to output twice, but then colours to output three times? The above would output one product but three publishers and colours... I think... maybe I should try it...

[Ken2k7]

You need to setup your tables a lot better.

 

Products

id

data

 

Publishers

id

data

product_id

 

** I don't know what relation colors have with either product or publisher, so I assume it'll go with product **

Colors

id

color

product_id

 

Then use this SQL:

SELECT pr.id AS product_id, pr.data AS product_data, pu.id AS publisher_id, pu.data AS publisher_data, co.id AS colors_id, co.color AS colors_color
FROM products pr
INNER JOIN publishers pu
     ON pu.product_id = pr.id
INNER JOIN colors co
     ON co.product_id = co.id
WHERE pr.id = 'some_id'

The WHERE clause should be modified. I'm not sure if you go by data or id, so I chose one by random.

[/code]