reorder rows

[rbrown]

I'm trying to setup up a delivery route and I have been racking my brain on how to do this...

What I want to do is have a database with following fields:

 

route_order

route_number

directions

left_house_number

left_house_street

right_house_number

right_house_street

 

And the way it will display is by based on the route_number, then it will display the order of the houses based on the driveways on the street and display directions when you need to turn. And if the driveways are across from each other will display on same line. and directions will display on a single line.

 

So for example the output would look like this.

For route_number: 68-24-678

 
route_order | left side # and st | right side # and st
1               | 1 abc road
2               | 3 abc road 
3               | 5 abc road     | 2 abc road (across road)
4               |                | 4 abc road
5               | 9 abc road     | 
6               | turn left on def drive (directions)
7               | 40 def drive
8               |                | 37 def drive
9               | 42 def drive
10              | 44 def drive
11                               | 33 def drive

I have the database and the output working. I can enter /edit the the data, but the problem is, for example, what if I want to add another house (8 abc road) in between route_order number 5 and 6?

 

If I input 6 then I have 2 route_order 6's. How would I add 1 to all the other records after route_number 6? So when it outputs the route the rest of the houses and directions after that record stay in the correct order?

 

Just in case you are wondering why I have the number and street separate, is some streets with houses on the corner have their driveway on another street. And it uses a drop down list to pull the streets so you don't have to retype the name in every time. And there is less room for error when it pulls the product delivery data from the customer record based on the street and house number.

 

 

Thanks in advance...

__________________

Bob

 

[rbrown]

While waiting for some help...

I did some more searching on google and found this...

 

http://www.phpfreaks.com/tutorial/php-custom-list-order

 

Did some changes to make it work the way I want.

It's not an automatic solution, but gave me some ideas where I can I think I can modify the script to allow an insert into the middle without having to page them up or down in the rows. And I can still tweak it by moving the rows up or down.

 

Thanks to CV!!!

 

 

[rbrown]

Also in case you are wondering how to insert the data where you want,

James Trowbridge gave me this answer this over at:

http://forums.codewalkers.com

 

 

/* Prepare the table to accept a new route_order '6' right after '5' */ 
UPDATE table1 SET route_order = route_order+1 WHERE route_order>=6; 
/* INSERT the new house with route_order 6 */ 
INSERT INTO table1 (route_order) VALUES (6);  

 

Now with both ways I can write a way to move the data up and down or be able to insert any where in the table.

 

Thanks again to both of you...