Ordering Menu Items Alphabetically

Posted on 02 January 2007 in
programming mysql How to Drupal cool

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

[adsense:468x60:4496506397]

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

ID	Sport	Weight	Row Count
13	golf	19	1
1	rugby	-2	2
4	soccer	11	3
20	swimming	-8	4

[adsense:468x60:4496506397]

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… This means we're half way to a solution!

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

ID	Sport	Weight	Row Count
13	golf	1	1
1	rugby	2	2
4	soccer	3	3
20	swimming	4	4

BRILLIANT! It worked!

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