Ordering Menu Items Alphabetically

Faced with a tedious task of ordering a set of rows using a weight column? Don't want to type the numbers out? Would rather find a difficult but more interesting method? I have the answer for you!

In Drupal, menu items (like the projects list on this site) are ordered by giving each one a weight. This means you can order the items in anyway you like! The problem I had was I imported about 60 menu items but not in alphabetical order. I didn't really want to type all the numbers out again so I sought out a less tedious but much more complicated method - its what I do!

As google is my friend, it helped me on my way to:
How to number rows in MySQL, by Xaprb

Using Xaprb's site and code I quickly produced a simple select query which ordered my list…

set @cnt = 0;

select
  mid,title,weight,
  @cnt := (@cnt + 1) as row_number
from menu
where pid=36
order by title;

This selected all menu ID's, title's, weight's and a row count column which incremented a variable called cnt, filtering the list by menu items with a parent ID of 36 and ordering by the title (default ascending). This produced a list like this (EXAMPLE):

This table shows that the only ordered columns are the title and row count, but the weight (which is used by Drupal) is NOT ordered&hellip This means we're half way to a sollution!

Unfortunately, with the UPDATE command, you cant use the variable column additionally as you would in a select (eg, above)… However I have learned that you can include the forumla part in the set section of the UPDATE… I shall Explain by example!

set @cnt = 0;
update menu set weight = @cnt := (@cnt+1) where pid=36 order by title;

Same situation as above, (filtering and ordering) - basically I want to update all menu items with a parent ID of 36, ordered by title in ascending order and I want to set the weight to the value of @cnt which itself is equal to its current value plus one

I ran that and was politely told that a number of rows had been updated, so I ran the select again and was shown:

BRILLIANT! It worked!

I hope this helps people out - certainly taken the mundanity out of that job!