editing dynamic menu php mysql

Question

I'm trying to build a dynamic menu (with sub menus) in Php and MySql. And trying to build dynamic editor for manipulate with this menu, create , delete and etc. I've stacked when creating menu category with it's position. To understand well take a look at mysql table structure =>

create table menu(
id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
sort_id UNSIGNED BIGINT NOT NULL,
title VARCHAR(20) NOT NULL,
etc ...);

I'm sorting menu categories with sort_id, and during creating I gave this column different values (for first time, by default) , because after creating another categories , I'm adding values by 1 and subtracting by 1 too , but I think it is pretty bad way doing it. Any ideas how to manage MySql table to define position of created categories (which would be forward or backward) ? thanks :)

In sort_id, just put the id of the parent menu, and put 0/-1 in the roots menus. (depending on how your sql manager starts the ids. — Jerska
– Jerska, Commented Aug 7, 2012 at 20:38
and what about the main menu category which doesn't have any parent menu ? — nanobash
– nanobash, Commented Aug 7, 2012 at 20:39
please give me a little example , I can not understand well , thanks beforehand :) — nanobash
– nanobash, Commented Aug 7, 2012 at 20:42

Ozzy · Accepted Answer · 2012-10-03 22:42:17Z

3

You shouldn't use the sort_id as @Jerska said because then you won't be able to sort your submenus & it will complicate/confuse your program logic. You can have a table like this instead

CREATE TABLE menu(
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id),
  parent_id INT,
  FOREIGN KEY(parent_id) REFERENCES menu(id),
  sort_id INT,
  title VARCHAR(100)
) ENGINE=InnoDB;

if the parent_id is set to null then it is a root menu.

This is an example menu for you:

Home & Garden
- Living Room
- Dining Room
- Bathroom
- Bedroom
- Garden & Conservatory
Electricals
- Sound & Vision
- Computing & Phones
- Home Appliances
- Small Appliances

It would be stored like this:

INSERT INTO menu
  (parent_id, sort_id, title)
VALUES
  (null, 1, "Home & Garden"),
  (null, 2, "Electricals"),
  (1, 1, "Living Room"),
  (1, 2, "Dining Room"),
  (1, 3, "Bathroom"),
  (1, 4, "Bedroom"),
  (1, 5, "Garden & Conservatory"),
  (2, 1, "Sound & Vision"),
  (2, 4, "Small Appliances"),
  (2, 3, "Home Appliances"),
  (2, 2, "Computing & Phones");

If you actually inserted them in that order, the ID of "Home & Garden" would be 1, and the ID of "Electricals" would be 2 (don't confuse with the sort_id) and the ID of "Living Room" would be 3, the ID of "Dining Room" would be 4... since we're auto-incrementing on the ID field.

So if you want to have a sub-category say in Sound & Vision, first you would find the ID, in this case it would be 8 and then you would add new records with the parent ID as 8.

Example sub-sub-category:

Electricals  
- Sound & Vision  
  * Televisions  
  * Audio

You would insert these records:

INSERT INTO menu
  (parent_id, sort_id, title)
VALUES
  (8, 1, "Televisions"),
  (8, 2, "Audio");

edited Oct 3, 2012 at 22:42

answered Aug 7, 2012 at 20:45

Ozzy

8,3327 gold badges58 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

nanobash Over a year ago

with this kinda table structure can I have multiple sub menus ? I mean only with this table ?

Ozzy Over a year ago

@Tornike what do you think happens if a menu has a parent_id pointing to another menu which has a parent_id pointing to a menu with parent_id of NULL?

Jerska Over a year ago

Well actually, in my mind, sort_id was used as a parent id. And then everything is fine. He can sort on it also.

nanobash Over a year ago

I think it is possible , but asked because I'm just new in MySql, and wanted to be sure, that it was possible :)

Ozzy Over a year ago

@Jerska Yes, but he should ALTER TABLE and add a column rather than use the sort_id which already has its own purpose. The purpose of the sort_id is not to SORT in the way you are saying, but to choose a specific order to display the menu in - which isn't possible when you use it to store the parent menu id

|

Jerska · Accepted Answer · 2012-08-07 20:47:28Z

3

In sort_id, just put the id of the parent menu, and for the root menus, just put 0/NULL.

Example : (sid = sort_id)

id|sid| title
1 | 0 | Menu1
2 | 0 | Menu2
3 | 1 | Submenu1of1
4 | 1 | Submenu2of1
5 | 2 | Submenu1of2
6 | 4 | Subsubmenu1of2of1

will refer to :

1 : Menu1
    3 : Submenu1of1
    4 : Submenu2of1
        6 : Submenu1of2of1
2 : Menu2
    5 : Submenu1of2

Now making the conversion between step 1 and step 2 is your part of the job.
Use recursivity, and you'll be fine. :)

edited Aug 7, 2012 at 20:47

answered Aug 7, 2012 at 20:38

Jerska

12.1k4 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

editing dynamic menu php mysql

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related