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.
Very good
Very good