CODEWITHSUNDEEP
phpmysql
David Hope
David Hope

Reputation: 1536

PHP: join tables query to create categories and subcategories?

I have a products table that looks like this:

id            di_make              di_model

1             Samsung               TV656  
2             Samsung               TV555
3              Sony                 LCD33

I have a makes table that looks like this (this is the table that i use as the category table):

id            names              

1             Samsung              
2             Samsung             
3              Sony

I need to create an output like this so when the category/make is clicked on, the dropdown will show the di_model(s) or Subcategories from the products table as subcategories:

http://jsfiddle.net/7NYhe/500/

I tried this query:

$sql3 ="SELECT DISTINCT products.di_make, products.di_model
FROM products
LEFT JOIN makes
ON products.di_make=makes.names";
$query3 = mysqli_query($db_conx, $sql3);
$existCount3 = mysqli_num_rows($query3);
if ($existCount3!=0) {

while($row3 = mysqli_fetch_array($query3, MYSQLI_ASSOC)){

$make_names = $row3["di_make"];
$make_model = $row3["di_model"];

//print "<li>".$make_names."</li>"; 

print '<li class="dropdown">'.$make_names.'
      <ul>
           '.$make_model.'
      </ul>
   </li>';


}
}

But this will create this:

http://jsfiddle.net/7NYhe/502/

So basically, it creates one category for each subcategory!

Could someone please advice on this issue?

HERE IS A WORKING SQL FIDDLE OF MY CODE:

http://sqlfiddle.com/#!9/4d9f68/1

Upvotes: 1

Views: 592

Answers (2)

Shubham Saini
Shubham Saini

Reputation: 182

you can use this query

SELECT products.di_make, GROUP_CONCAT(products.di_model) as di_model 
FROM products LEFT JOIN makes ON products.di_make=makes.names 
GROUP BY products.di_make

you will fetch data like this by using it 

di_make  di_model
Samsung  TV656,TV555
Sony     LCD33

you will get di_model as a string if a category have more than one product.now you can explode that string.

$subcategories = explode(",",$make_model)
'<li class="dropdown">'.$make_names.'
    <ul>';
      foreach($subcategories as $sub){

        '<li>'.$sub.'</li>';

      }

    '</ul>
</li>';

you can implement your php like it.

Upvotes: 4

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this query - 

SELECT products.di_make, MAX(products.di_model)
FROM products 
JOIN makes ON products.di_make=makes.names
GROUP BY products.di_make

Upvotes: 1

Related Questions