Mysql: update weight by an auto-incremented string created on the fly

I had a problem with Drupal6 by sorting tags. Mysql can sort or place some non-ascii letters correctly but PHP can not.

Example: I have a list of cities and I want to sort them by first letter. I want that "ívori" and "îwata" beeing listet with "Ibiza" under "i". PHP places both ívori and îwata after Z. To prevent this I update "weight" manually with mysql and sort them by weight instead of alpha.

select @my_temp_counter:=0;
update `term_data` set weight = (@my_temp_counter:=@my_temp_counter+1) order by name;

As you see we declare a mysql string for the session first and then sort them and then let update every row of db.table with an auto-incremented value.

Mysql: update weight by an auto-incremented string created on the fly

Add new comment