Joomla Backend menu re-ordering alphabetically with mysql

I've been trying to figure out a way to re-order a menu that we have so it will be alphabetical. This开发者_高级运维 menu has about 500 items and is strictly used for URL structure, but we have another display menu that will be using MenuLinks and we have to choose all of those from a dropdown in the backend and it's extremely hard to figure out. I've been trying to create a mysql query that will do it but have had no avail.

along the lines of

UPDATE jos_menu set ordering = ordering+1 WHERE menutype = 'menu-name' ORDER BY name ASC

but of course it doesn't work. Just curious if anyone has figured this out.

Maybe if i manipulate the admin to display the dropdown menu (when editing a menu item) to display alphabetically, which is kind of does for the Level 0 items but not its children.

A way you could try is:

• copy the table structure with PHPMyadmin
• insert into newtable from oldtable where menutype='menu-name'
• drop autonumber primary key
• set autonumber to 0
• sort table physically by name (
• reorder table physically by menu-type (ALTER TABLE foo ORDER BY key; )
• re-add autonumber primary key
• update newtable, set ordering=menu_id
• re-insert into oldtable

HTH, Jochen