I've been working on a content management system that includes the ability for users to create and manage their own navigation menu. This includes specifying the order in which the main menus and sub-menus that belong to these main menus appear.
This is a simplified example of two tables. The first, maimenus would contain the names of main categories such as DOGS and CATS. The second table, submenus, may contain the names of sub-categories that belong to DOGS and CATS, for example Dog Toys, Dog Treats, Cat Food, Cat Litter
mainmenus ---- table 1
submenus ---- table 2
Lets look at some sample data for Table 1, mainmenus :
ID TITLE SORTORDER
19 DOGS 2
24 CATS 1
Table 2, submenus, would look something like :
ID MAINMENUID TITLE SORTORDER
1 19 Dog Toys 1
2 19 Dog Treats 3
3 19 Dog Houses 2
4 24 Cat Food 1
5 24 Cat Litter 2
Typically I would do something like "select id,title,sortorder from submenus where MAINMENUID=19 order by sortorder";
This would produce :
ID Title SORTORDER
1 Dog Toys 1
3 Dog Houses 2
2 Dog Treats 3
Ok, now let me get the the meat of my question. If I delete Dog Houses, the sortorder being 2, is their a way to have mysql renumber the sortorder field only for the items that have only a value of 19 (DOGS) for the mainmenus field? The end result being if I delete Dog Houses the sortorder for Dog Toys would be 1, the sortorder for Dog Treats would become 2.
I was wondering if I could do this purely in MySql?
Note: I found an effective and easy way of achieving this with PHP. But, was thinking that if I could have MySql do it that it would be faster.