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

by


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";

2

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