[SOLVED] Mysql Join : on three tables

[avvllvva]

I have to make a product listing page. I wish to get a output like this

 

PRODUCT	CATEGORY
Prod1	        Cat1
Prod2	        Cat1,Cat2,Cat3
Prod3	        Cat1,Cat2

 

Here u can see that product2&3 have multiple categories.

 

Now come on to MySql section

 

there are 3 tables

1. tbl_product (product details)

2. tbl_category (category details)

3. tbl_map_product_category (mapping table for both, here Im keeping the category reference of      each products and have 2 fields product_id & category_id)

 

This is my query,

 

SELECT 
P.product_name, C.category_name
FROM 
tbl_product P
JOIN 
tbl_map_product_category MPC
ON 
P.product_id = MPC.product_id
JOIN 
tbl_category C
ON 
C.category_id = MPC.category_id

   

There are 2 cases

Case 1 : Product with single category :- in this case above query is working fine and I can able to produce the output what I mentioned top, from each resultant row.

Case 2:  Product with multiple category :- this time query behaviour is different and not giving the output what I wish. its giving  two rows for each execution,

bcoz there are multiple category.

 

 

In short how can I get multiple category within each resultant row?

 

guide me how to modify my query to achieve this ?

 

Or if there any other way ?

 

should I change my whole table structure to achieveing this one to many relationship, any other easy method ?

please help..

[TeNDoLLA]

One way would be to organize the data after retrieving from database. For example like this.

<?php
// Get data from db.
$sql = "SELECT p.product_name, c.category_name
	  FROM products p  
	  JOIN map_product_category map ON map.product_id = p.product_id
	  JOIN categories c ON c.category_id = map.category_id";
$result = mysql_query($sql);

// Organize data.
$data = array();
while ($row = mysql_fetch_object($result))
{
if (key_exists($row->product_name, $data))
{
	$data[$row->product_name] .= ', ' . $row->category_name;
}
else
{
	$data[$row->product_name] = $row->category_name;
}
}

// Output data.
echo '<table border="1"><tr><th>Products</th><th>Categories</th></tr>';
foreach ($data as $key => $value)
{
echo '<tr><td>'. $key .'</td><td>'. $value .'</td></tr>';
}
echo '</table>';

[avvllvva]

Okay its fine, but if there any other way to do without using php codes? I mean mysql itself.

[kickstart]

Hi

 

What you need to use is "group_concat", to sum up the text fields.

 

All the best

 

Keith

[avvllvva]

Thank you kickstart.

 

Its working perfectly, this is the query

SELECT 
P.product_name, group_concat(C.category_name)
FROM 
tbl_product P
JOIN 
tbl_map_product_category MPC
ON 
P.product_id = MPC.product_id
JOIN 
tbl_category C
ON 
C.category_id = MPC.category_id
GROUP BY 
        P.product_id

 

thnx guys.