Re-ordering rows and adjusting orderid

[Kinger1613]

Hi everyone. I'm putting together a client list for my company and I need to be able to move clients around from day to day and also change the order they are listed. My only problem is I can't figure out a way to adjust the orderid once moving them.

 

My database is set up something like this:

 

id | orderid | driverid | day | name | email

1        1            1        Mon     -          -

2        3            1        Tue     -          -

3        2            2        Wed     -          -

4        4            1        Mon     -          -

 

I need to be able to edit a rows orderid, lets say from 5 to 3 and re-order everyone so that there are no orderid duplicates and no gaps. I was thinking I need to submit the form with the changed orderid and if the new orderid is different from the old orderid, a query will select and add 1 to the rows with an orderid greater than the new one submitted and only for clients on that drivers day. Once that is done a new query will re-adjust all the orderid's again and re-order them so there are no gaps.

 

Am I on the right track here? How would I write this code out? I feel as if im doing it all wrong. Any help is greatly appreciated. Thanks

if ($orderid1 != $orderid2) {
		
  $result = mysql_query("SELECT * FROM clients WHERE driverid = '$driverid' and day = '$day'") or die (mysql_error());
  while($row = mysql_fetch_assoc($result)){
			
    $neworderid = $row['orderid'] + 1;
    mysql_query("UPDATE clients SET 
      orderid = '$neworderid',
    WHERE orderid > '$orderid2' and clientid = '$row[clientid]'");
				
  }
			
}

Edited December 13, 2014 by Kinger1613

[Barand]

What determines that a particular client should move from position 5 to position 3?

 

If it is a particular attribute that could be stored or calculated then you could use that in an ORDER BY clause and save yourself the effort of moving them manually.

[Kinger1613]

I would have to move the client manually. Each client is in the order they are based on the order we visit them on that particular day. Let's say I move a client to a different day or a different driver. That driver will want them in a certain spot on the list.

[kicken]

How are you adjusting the order? If your using some kind of Javascript to re-order things on the website, then just have that code re-number everything when the order changes and on submit update each item in the database with the new order.

[Kinger1613]

The order is being adjusted via a dropdown box that will allow you to move the client before or after another client inside that page. No javascript. This is all php.

[QuickOldCar]

As kicken stated, update each a new order in the database upon doing the submit

 

Query all your users and their orders

Change the selected users order to an insane high number would never use

Sort the new array by lowest order and update them all with an update set query but using the order ranges you want.

 

My first impression looking at this was to use a timestamp/date based system.

Update the timestamp to the current timestamp the selected user.

The biggest difference in time the database compared to the current time would be lowest order.

Edited December 14, 2014 by QuickOldCar