JOIN in the same table and FOREACH with IMPLODE to get menu

user_5753599 0
Viewed 127

Table: categories

╔═════════════╦═══════════════════╦═════════════════╗
║ id_category ║ category_name     ║ category_father ║
╠═════════════╬═══════════════════╬═════════════════╣
║  1          ║ Home              ║   0             ║
║  2          ║ Category_1        ║   1             ║
║  3          ║ Category_2        ║   1             ║
║  4          ║ Category_3        ║   1             ║
║  5          ║ Category_4        ║   1             ║
║  6          ║ Category_5        ║   1             ║
║  7          ║ Sub_Category_1    ║   2             ║
║  8          ║ Sub_Category_2    ║   2             ║
║  9          ║ Sub_Category_3    ║   2             ║
║  10         ║ Sub_Category_4    ║   2             ║
║  11         ║ Sub_Category_5    ║   3             ║
║  12         ║ Sub_Category_6    ║   3             ║
║  13         ║ Sub_Category_7    ║   3             ║
║  14         ║ Sub_Category_8    ║   3             ║
║  15         ║ Sub_Category_9    ║   3             ║
╚═════════════╩═══════════════════╩═════════════════╝

I am using meekro.class to consult my database, right now I'm building the menu of my application, but I need to do JOIN to the same table to show the category_name instead of the id of category_father and I can not.

I also need to show in list form the main categories and below the subcategories. I intend to use only one level of sub categories so this table structure should work.

This is my query:

$querycategories = DB::query("SELECT * FROM categories");
foreach ($querycategories as $row) {
    echo "<b>" . $row['category_name'] . "</b>";
    echo "<ul>";
        echo "<li>" . $row['category_father'] . "</li>";
    echo "</ul>";
}

Of course, with this consultation, I get a disaster, that's why I come to you to get help!

This is what I hope to obtain:

Category_1

  • Sub_Category_1
  • Sub_Category_2
  • Sub_Category_3
  • Sub_Category_4

Category_2

  • Sub_Category_5
  • Sub_Category_6
  • Sub_Category_7
  • Sub_Category_8
  • Sub_Category_9

Category_3

Category_4

Category_5

I hope you can help me, it takes almost 1 hour to write the question, please, take 5 minutes of your day to help me, thanks in advance!

menu mysql php
2 Answers

LEFT JOIN will deliver the necessary null values to build your unordered list.

I say use this: (http://sqlfiddle.com/#!9/6e7fe/3)

SELECT A.category_name AS parent, B.category_name AS child
FROM categories A 
LEFT JOIN categories B on A.id_category=B.category_father
WHERE A.category_father=1
ORDER BY parent

Output:

╔════════════╦════════════════╗
║   parent   ║     child      ║
╠════════════╬════════════════╣
║ Category_1 ║ Sub_Category_1 ║
║ Category_1 ║ Sub_Category_2 ║
║ Category_1 ║ Sub_Category_3 ║
║ Category_1 ║ Sub_Category_4 ║
║ Category_2 ║ Sub_Category_5 ║
║ Category_2 ║ Sub_Category_6 ║
║ Category_2 ║ Sub_Category_7 ║
║ Category_2 ║ Sub_Category_8 ║
║ Category_2 ║ Sub_Category_9 ║
║ Category_3 ║ (null)         ║
║ Category_4 ║ (null)         ║
║ Category_5 ║ (null)         ║
╚════════════╩════════════════╝

PHP Code: (Demo)

$querycategories=[
    ['parent'=>'Category_1','child'=>'Sub_Category_1'],
    ['parent'=>'Category_1','child'=>'Sub_Category_2'],
    ['parent'=>'Category_1','child'=>'Sub_Category_3'],
    ['parent'=>'Category_1','child'=>'Sub_Category_4'],
    ['parent'=>'Category_2','child'=>'Sub_Category_5'],
    ['parent'=>'Category_2','child'=>'Sub_Category_6'],
    ['parent'=>'Category_2','child'=>'Sub_Category_7'],
    ['parent'=>'Category_2','child'=>'Sub_Category_8'],
    ['parent'=>'Category_2','child'=>'Sub_Category_9'],
    ['parent'=>'Category_3','child'=>null],
    ['parent'=>'Category_4','child'=>null],
    ['parent'=>'Category_5','child'=>null]
];

$cat=null;
foreach ($querycategories as $row){
    if($cat!==$row['parent']){
        echo "<b>{$row['parent']}</b>";
    }
    if(!is_null($row['child'])){
        echo "<ul><li>{$row['child']}</li></ul>";
    }
    $cat=$row['parent'];
}

Output:

<b>Category_1</b>
<ul>
  <li>Sub_Category_1</li>
</ul>
<ul>
  <li>Sub_Category_2</li>
</ul>
<ul>
  <li>Sub_Category_3</li>
</ul>
<ul>
  <li>Sub_Category_4</li>
</ul>

<b>Category_2</b>
<ul>
  <li>Sub_Category_5</li>
</ul>
<ul>
  <li>Sub_Category_6</li>
</ul>
<ul>
  <li>Sub_Category_7</li>
</ul>
<ul>
  <li>Sub_Category_8</li>
</ul>
<ul>
  <li>Sub_Category_9</li>
</ul>

<b>Category_3</b>

<b>Category_4</b>

<b>Category_5</b>
user_29434030

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE categories
    (`id_category` int, `category_name` varchar(14), `category_father` int)
;

INSERT INTO categories
    (`id_category`, `category_name`, `category_father`)
VALUES
    (1, 'Home', 0),
    (2, 'Category_1', 1),
    (3, 'Category_2', 1),
    (4, 'Category_3', 1),
    (5, 'Category_4', 1),
    (6, 'Category_5', 1),
    (7, 'Sub_Category_1', 2),
    (8, 'Sub_Category_2', 2),
    (9, 'Sub_Category_3', 2),
    (10, 'Sub_Category_4', 2),
    (11, 'Sub_Category_5', 3),
    (12, 'Sub_Category_6', 3),
    (13, 'Sub_Category_7', 3),
    (14, 'Sub_Category_8', 3),
    (15, 'Sub_Category_9', 3)
;

Query 1:

SELECT 
    c1.category_name as parent_name
  , c2.category_name as child_name
  , c1.id_category as parent_id
  , c2.id_category as child_id
FROM categories c1 
inner join categories c2 on c1.id_category = c2.category_father
order by parent_id, child_name

Results:

| parent_name |     child_name | parent_id | child_id |
|-------------|----------------|-----------|----------|
|        Home |     Category_1 |         1 |        2 |
|        Home |     Category_2 |         1 |        3 |
|        Home |     Category_3 |         1 |        4 |
|        Home |     Category_4 |         1 |        5 |
|        Home |     Category_5 |         1 |        6 |
|  Category_1 | Sub_Category_1 |         2 |        7 |
|  Category_1 | Sub_Category_2 |         2 |        8 |
|  Category_1 | Sub_Category_3 |         2 |        9 |
|  Category_1 | Sub_Category_4 |         2 |       10 |
|  Category_2 | Sub_Category_5 |         3 |       11 |
|  Category_2 | Sub_Category_6 |         3 |       12 |
|  Category_2 | Sub_Category_7 |         3 |       13 |
|  Category_2 | Sub_Category_8 |         3 |       14 |
|  Category_2 | Sub_Category_9 |         3 |       15 |
user_20677530
Related