Advanced Sorting in MySQL - Help?

[holmescreek]

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

id
title
sortorder


submenus ---- table 2

id
mainmenuid
title
sortorder



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.

[r937]

to answer the question you asked, no

there is a way with standard sql to renumber column values, but it is exceedingly complex, and mysql couldn't handle it because it can't do subqueries

i have exactly the same mainnav/subnav structure as you, including a sequence column, but with two differences --

1. both levels are in the same table

this means i can go to three levels easily if i want -- right now two levels of navigation is perfect, but i don't have to modify the database if i want three

2. sequence numbers are assigned 10, 20, 30, etc.

now i can easily insert between existing entries or at the beginning, or move entries around, without renumbering

eventually, a renumbering might be necessary, but only if you didn't choose big enough sequence number intervals to start with

so keep your php routine handy, and hope you never have to use it

[shane]

Could you not just run 3 queries when deleting the submenu?

//NB: I don't know if this syntax can be used with MySql

//Read these values into PHP variables and use in last sql statement
SELECT	mainmenuid,
	sortorder
FROM	submenus
WHERE	submenuid = [submenuid to delete]

DELETE
FROM	submenus
WHERE	submenuid = [submenuid to delete]

UPDATE	submenus
SET	sortorder = (SM.sortorder - 1)
FROM	submenus SM
WHERE	SM.sortorder > [deleted submenu sortorder]
AND	SM.mainmenuid = [deleted submenu mainmenuid]

It's convoluted and you'd need to make sure that you always used the same function for deleting submenus. Also, as the sortorder just determines the order is there really any need to update the sortorder as the menus will still be ordered correctly.

(Hasn't MySql got Stored Procedures or Triggers yet?)