Php get subcategories by category id with mysql query

Question

This is my db structure

Categories table
id    title
-----------
1     cars
2     city
3     names
------------
subcategory table
id    cat_id    title
1     1         BMW
2     1         Kia
3     2         Moscow
4     1         Passat
5     2         London
6     3         John
----------------------

Now Im getting categories and printing with foreach like this:

$myquery = query("Select * From categories"); // These functions only for example

$array = mysql_fetch_array($myquery); // These functions only for example

foreach($array as $cat){

    echo $cat['title'];
    echo "<br />";

    // in here must be again foreach or other loop to print subcats belongs to
    // this category id, otuput like this:
    // Cars 
    // -- BMW
    // -- KIA
    // -- Passat
    // City
    // -- Moscow and etc...

}

I dont want use new query inside foreach, I want do this with mysql JOIN but i don't know how. Who can help me?

make the join statement and order by category.id, then in the loop check if you have had that coming category already -> print only the sub category. If you haven't had the main category yet, also print that. — Jeff, Dec 10 '17 at 20:03
or you put it all in a 2-dimentional array first (with the category.id as main key). — Jeff, Dec 10 '17 at 20:03

miile7 · Answer 1 · 2017-12-11T08:13:04.067

1

The mysql join would be:

SELECT subcategory.*, categories.title as cat_title FROM subcategory JOIN categories ON subcategory.cat_id = categories.id ORDER BY subcategory.cat_id;

This will get you a table which contains all the subcategory items with additional category information

The php would look like this then:

$result = mysql_fetch_array($myquery);
foreach($result as $row){
    // this is the category name
    print($row['cat_title']);
    // the title of the subcategory item
    print($row['title']);
}

If you do not want to print the category name each time check out this post how to sort a table by one colum. Change the array to an multidimensional array so you can use two foreachs. Php would look like this then:

$categories = array();
// result comes from the SELECT
foreach($result as $row){
    if(isset($row['cat_title']) && !empty($row['cat_title'])){
        if(!isset($categories[$row['cat_title']]) || !is_array($categories[$row['cat_title']])){
            $categories[$row['cat_title']] = array();
        }

        array_push($categories[$row['cat_title']], $row);
    }
}

Now you can go through the categories with a foreach:

foreach($categories as $category => $items){
    print($category);
    foreach($items as $item){
        print("\t".$item);
    }
}

edited Dec 11 '17 at 08:13

answered Dec 10 '17 at 20:04

miile7

2,547
3
23
38

and how can I echo category and subcat titles separately, can u show? – Gizli Hesab Dec 10 '17 at 20:08
category table doesn't have cat_id column did u write it by mistake? – Gizli Hesab Dec 10 '17 at 20:09
It this solution all cats will be printed cabcategory count times. – Torv Dec 10 '17 at 20:14
Added the php code, there also were 2 mistakes in the SELECT, your table is called "categories", I wrote it wrong. Also there are two "title" columns, to access both of them I renamed the other table – miile7 Dec 10 '17 at 20:14
@Torv yes, it will. But if he wants to do all with one SELECT he has to change the `foreach` to loop through the categories first. – miile7 Dec 10 '17 at 20:15
@miile7 He dont want query in loop, and its good idea. And yes its can not be solved in one query for this conditions. – Torv Dec 10 '17 at 20:19
1

you should add an order by to the select statement to ease the things – Cleptus Dec 10 '17 at 20:23
it gives output like this: KIA --Cars BMW --Cars PASSAT --Cars Moscow --City and etc... – Gizli Hesab Dec 10 '17 at 20:51

Torv · Accepted Answer · 2017-12-10T20:35:27.053

For your task may be a good solution is two query, one for all categories, and one for all subcategories with joined cats(or without).

First loop for all cats and second loop for subcats in this cat.

foreach ($cats as  $category) {
  echo $category['title'];
  foreach ($subcats as $subcat) {
    if ($subcat['cat_id'] !== $cat['id']) {continue;}
    echo '--'.$subcat['title']
  }
}

Another variant with one query is additional array with already printed categories like

SQL: As @miile7 and bradbury9 offered with order

SELECT subcategory.*, categories.title as cat_title FROM subcategory JOIN categories ON subcategory.cat_id = categories.id ORDER BY subcategory.cat_id;

PHP:

$printedIds = [];
foreach ($subcats as  $subcat) {
  if (!in_array($subcat['cat_id'] ,$printedIds)) {
    $printedIds []= $subcat['cat_id'];
    echo $category['title'];
  }

  echo '--'.$subcat['title'];
}

P.S. Its good idea name tables in one style all: all in plural or all in a single.

Php get subcategories by category id with mysql query

2 Answers2