1

I've been working on this new project and I need help about this situation. I have sql database with two tables: menu and submenu.

The structure of tables is :

Menu : id | title | order

Submenu : id | title | parentId

When I want to generate menu item and submenu item below I use two simple query, is there any simplier way with joining table.Basically I want this :

  1. Menu Item

    + Submenu Item 1
+ Submenu Item 2
+ Submenu Item 3

  2. Menu Item

    + Submenu Item 1
+ Submenu Item 2
+ Submenu Item 3
Community
  • 1
  • 1
  • You probably really want to have a read of [this question and answer](http://stackoverflow.com/q/12475850/1450077) that I posted for just this reason. It will show you how to link tables to get the information you need from them. – Fluffeh Jun 28 '15 at 01:01
  • you don't need 2 tables , one table is enough see http://stackoverflow.com/questions/7569399/get-a-recursive-parent-list m this is what you are looking for – Nassim Jun 28 '15 at 01:03
  • I need 2 tables for another reasons, can you help if you can? @Fluffeh I try ofcourse but somehow i menage to display only one same submenu in all menu :/ –  Jun 28 '15 at 01:05

2 Answers2

0

let's say the database looks like this 

CREATE TABLE menu
    (`id` int, `title` varchar(15), `order` int)
;

INSERT INTO menu
    (`id` , `title` , `order` )
VALUES
    (1, 'itstuff', 100),
    (2, 'book', 200),
    (3, 'toy', 300),
    (4, 'car', 400),
    (5, 'clothes', 500),
    (6, 'stuff', 600),
    (7, 'otherstuff', 700),
    (8, 'otherotherstuff', 900)
;


CREATE TABLE submenu
    (`id` int, `title` varchar(10), `order` int, `parentid` int)
;

INSERT INTO submenu
  (`id` , `title` , `order` , `parentid` )

VALUES
   (1, 'laptop', 100, 1),
    (2, 'mouse', 200, 1),
    (3, 'printer', 300, 1),
    (4, 'lcd screen', 400, 1),
    (5, 'volvo', 500, 4),
    (6, 'toyota', 600, 4),
    (7, 'bmw', 700, 4),
    (8, 'ford', 900, 4)
;

Query

SELECT
submenu.title
FROM
submenu
INNER JOIN menu ON submenu.parentid = menu.id
WHERE
menu.title = "itstuff"

this query will return all the submenus that belong to the menu "itstuff"

Result

[title]
laptop
mouse
printer
lcd screen

DEMO

PHP code for creating the menu 

<?php

// Create connection
$db = new mysqli('localhost', 'username', 'password', 'database_name');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
};


// Perform queries
$sql1 = 'select * from menu';
$result1 = $db->query($sql1);

echo "<ul>";
foreach ($result1 as $menu)
{
     echo "<li>".$menu["title"];
     echo "<ul>";
     $menu_title = $menu["title"];

    $sql2 = 'SELECT
    submenu.title
    FROM
    submenu
    INNER JOIN menu ON submenu.parentid = menu.id
    WHERE
    menu.title = "'.$menu_title.'"';    

    $result2 = $db->query($sql2);
    foreach ($result2 as $submenu)
    {
        echo "<li>".$submenu["title"]."</li>";
    }
     echo "</ul>";
     echo "</li>";
}
echo "</ul>";


$db->close();

?>

result should look like this 

<ul><li>itstuff<ul><li>laptop</li><li>mouse</li><li>printer</li><li>lcd screen</li></ul></li><li>book<ul></ul></li><li>toy<ul></ul></li><li>car<ul><li>volvo</li><li>toyota</li><li>bmw</li><li>ford</li></ul></li><li>clothes<ul></ul></li><li>stuff<ul></ul></li><li>otherstuff<ul></ul></li><li>otherotherstuff<ul></ul></li></ul>

Hope that helps

Nassim
  • 2,879
  • 2
  • 37
  • 39
  • ok, but I want to display all menu items and if menu item have submenu items i want to display below if you know what i mean –  Jun 28 '15 at 14:48
  • that's easy, you make a foreach loop and echo the submenu for each menu using
  • , maybe ill have sometime later and explain in more details,
    • – Nassim Jun 28 '15 at 14:58