CODEWITHSUNDEEP
phpmysql
nanobash
nanobash

Reputation: 5500

editing dynamic menu php mysql

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

Upvotes: 3

Views: 756

Answers (2)

Ozzy
Ozzy

Reputation: 8312

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

Upvotes: 3

Jerska
Jerska

Reputation: 12002

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

Upvotes: 3

Related Questions