PHP - script that add an ordered field on database [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Add a field and insert an increment value

I have this table on my database in mysql:

id   ref     data
1    111     data1
2    111     data2
3    111     data3
4    111     data4
5    222     data1
6    222     data2
7    222     data3
8    333     data1
9    333     data2

and I'd like to insert a new field, called for example order, where for each ref, I'll set a crescent va开发者_JAVA技巧lue. So the output should be :

id   ref     data    order
1    111     data1   1
2    111     data2   2
3    111     data3   3
4    111     data4   4
5    222     data1   1
6    222     data2   2
7    222     data3   3
8    333     data1   1
9    333     data2   2

how can I do it? Update seems to me undefined.. so I should order them following the id of the table...

---

Do not name the field order that is a reserved word in MySQL and it is a hassle to use reserved words as field names. Let's name it rank.

-- first add new field.
alter table table_name add column rank int(3);

-- then fill the field with a rank.
set @rank = 0;
set @ref = null;
update table_name set rank (
  select rank from (
    select 
      if(@ref <> ref,@rank:= 1,@rank:= @rank+1) as rank
      if(@ref <> ref or @rank = 1,@ref:= ref, ref) as dummy
    from (
      select ref from table_name order by ref, id
    )

  )
);

-- use the following select to get the next rank to insert.
select @newrank:= max(rank)+1 as newrank from table_name where ref = '333';

---

If you want to add the new field to your table, here is the syntax (although I am not sure I fully understand your question):

alter table `table_name` add column `order` int(3);