如何在php中维护显示顺序字段 [英] how to maintain display order field in php

问题描述

我想在数据库中维护一个整数字段用于显示顺序，这将是唯一的(显而易见)

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

现在假设我在数据库中有 10 条记录，如果我更新第 10 条记录并将显示顺序设置为 10 到 1，那么从 1 到 9 的所有记录都会增加一个，比如显示顺序 1 将变为 2、2 到 3、3 到 4、4 到 5 ....

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

那么如何维护呢?

想要任何优化解决方案，如果我们有 1000 条记录，那么我们无法对所有 999 条记录触发更新查询.

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

请帮帮我

推荐答案

你为什么不试试函数或存储过程..这两个足够酷来处理这种情况..让我们给出试试下面的..希望它会帮助你..

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

如果您需要将 O_nbr(订单号)从 10 移到 1，您只需将该方法调用为

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 

功能代码如下 -

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 ;

您可以将 DisplayOrder 替换为您的表 nane 并将 O_nbr 替换为您的列名以重新排序

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

复制代码并在您的mysql数据库查询窗口中运行..然后使用上述签名来移动顺序(DisplayOrder表中的O_nbr)..

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

如果您尝试以 0 或更少的值运行，或任何不合适的条件，它将返回错误消息

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"

这篇关于如何在php中维护显示顺序字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！