How to weigh terms alphabetically

Posted on 07 August 2007 in
programming mysql Drupal cool

Following on from my Numbering Rows in MySQL article, today I needed to populate the weight column of the term_data table for about a hundred terms so that they incremented in alphabetical order. This was, on the face of it, a very tedious task; click edit, select weight from drop-down, submit, 'goto 1'.

As I said in my Numbering Rows in MySQL article, I don't like tedious - but I do like over-complicated methods which actually make my life more interesting and less tedious... Enter a nifty MySQL script!

This is basically the same as the Numbering Rows in MySQL tip, except this works on the term_data table instead of menu. Its defines a variable and updates the table, setting the weight to the variable value before incrementing it.

SET @cnt = 0;

UPDATE term_data
SET weight = @cnt := (@cnt+1)
WHERE vid=2
ORDER BY name;

If you use SQLyog, as I do, you must hit the button to Execute All Queries (the double green arrow, or Shift+F5 for the shortcut-minded ones). If you don't do this you end up sitting there like a lemon for a few minutes wondering why no rows are being updated.

I might be tempted in the future to turn this into a module where you can set all the weights for terms in a given vocabulary based on an alphabetical order.