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 :)

Answer 1

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");