如何在php中维护显示顺序字段

Question

I want to maintain one integer field in database for display order,which will be unique(obvious)

now lets say I have 10 records in database, and if I will update 10th record and will set display order from 10 to 1, so all records from 1 to 9 will increment bye one,like display order 1 will become 2, 2 to 3,3 to 4, 4 to 5 ....

so how to maintain this?

wants any optimize solution, if we have 1000's record then we can not fire update query for all 999 records.

Please help me

Answer 1

Why don't you try with function or stored procedure.. these two are enough cool to handle such situations.. lets give a try to below one.. hope it will help you out..

Remember I am considering following two parameters

table name - DisplayOrder
column name to reorder = O_nbr

if you need to shift the O_nbr (order number) from 10 to 1 you just need to call the method as

select UpdateOrder(10,1);
// it will return you the proper message either updated or not.. or whatever 

the function code is as below -

DELIMITER $$
create function UpdateOrder (fromOrder int, toOrder int) 
RETURNS varchar(50)
DETERMINISTIC
BEGIN 
  DECLARE reslt varchar(50);
  DECLARE rowCount int;
  SET reslt = 'Error Occured.';
  if( fromOrder < 1) then
    SET reslt='fromOrder can not be less then 1';
  elseif(toOrder < 1) then
    SET reslt='toOrder can not be less then 1';
  else  
    select count(*) into rowCount from DisplayOrder;        
    if(rowCount < fromOrder) then
        SET reslt = concat('Sorry check again, we have only ',rowCount, ' records');
    elseif(rowCount < toOrder) then
        SET reslt = concat('Sorry check again, we have only ',rowCount, ' records');
    else
        if(fromOrder = toOrder) then
            SET reslt = 'No Changes were made.';
        else
            if(fromOrder > toOrder) then
              update DisplayOrder set O_nbr = 0 where O_nbr = fromOrder;
              Update DisplayOrder set O_nbr = O_nbr+1 where O_nbr < fromOrder AND O_nbr > (toOrder-1) order by O_nbr desc;
              update DisplayOrder set O_nbr = toOrder where O_nbr = 0;
            end if;
            if(fromOrder < toOrder) then
              update DisplayOrder set O_nbr = 0 where O_nbr = fromOrder;
              Update DisplayOrder set O_nbr = O_nbr-1 where O_nbr > fromOrder AND O_nbr < (toOrder+1) order by O_nbr asc;
              update DisplayOrder set O_nbr = toOrder where O_nbr = 0;
            end if;
            SET reslt = 'Successfully done';
        end if;
    end if;         
  end if;     
  RETURN reslt;
END$$
DELIMITER ;

You can replace the DisplayOrder with you table nane and O_nbr with you column name to reorder

copy the code and run it in you mysql database query window.. then to shift the order (O_nbr in DisplayOrder table) use the above described signature..

Select UpdateOrder(fromOrder,  toOrder);

if you try to run with 0 or less value, or any unsuitable condition, it will return you the error message

Select UpdateOrder(10, 0);
//returns "toOrder can not be less then 1"
Select UpdateOrder(11, 1);
//return "Sorry check again, we have only 10 records"
Select UpdateOrder(5, 5);
//returns "No Changes were made."

if you pass the correct parameter..

Select UpdateOrder(10, 2);
//returns "Successfully done"