CODEWITHSUNDEEP
phpmysql
Er.KT
Er.KT

Reputation: 2860

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)

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

Upvotes: 2

Views: 516

Answers (3)

Deepak Sharma
Deepak Sharma

Reputation: 4170

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"

Upvotes: 1

Lo&#239;c
Lo&#239;c

Reputation: 11943

You can use bigger values for display, like say :

apple : 1 000 000

orange : 2 000 000

banana : 3 000 000

Now if I want to add potato in position 2 (n=2) I take n (=> 2 : orange) and (n-1)(=> 1 : apple) position and divide them by 2 to get the new potato position :

(1 000 000 + 2 000 000) / 2

= 1 500 000

apple : 1 000 000

**potato : 1 500 000**

orange : 2 000 000

banana : 3 000 000

This will work a pretty long time, and if at some point you feel like you've exhausted this schema, you can still multiply all values by 1000.

You may as well keep using low value (1, 2, 3, ...) and define their type as DOUBLE, so they will never collide when dividing them by 2.

EDIT : About placing banana in position 1 :

Using formula (n+(n-1))/2:

Here n = 1 (new position).

Our data is : 

apple : 1 000 000

potato : 1 500 000

orange : 2 000 000

banana : 3 000 000

(1 000000+(0))/2

= 500 000

I give the order 500 000 to banana :

**banana : 500 000**

apple : 1 000 000

potato : 1 500 000

orange : 2 000 000

Yet another solution : create a text field that would be an indexer and contain all ids.

"1,2,3,4,5,6,7"

Now If I want to put element id 8 between 3 and 4, I simply have to replace ",4," by ",8,4,".

Though it's more complicated in terms of computing to find element at index n so I'd personnaly use the first solution.

Upvotes: 2

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You can display data in any order without changing the id field value in the database.
I don't think it would be necessary to show the value of ID to the end user, but use only for internal reference.

You can try like this: 

select * from my_table
order by field( id, 10 ), id asc

This causes to fetch row with id value 10 on top and others next in ascending order.

You can specify any number of values in field to show in that specific order on top of results.

Upvotes: 0

Related Questions