PHP Sortable List

[steroyle]

I want to be able to change the order_id of a list of page names in my database from a php page.

id |  name         | order_id
------------------------------
1  |  welcome      |  1
2  |  about        |  2
3  |  information  |  3
4  |  contact      |  4

The php page lists all 4 page name with an up and a down arrow link next to each one.

I want to be able to click either arrow and for it to update the order_id accordingly in the database.

I have seen some examples of doing this with ajax and drag and drop but it is too much for what I need.

Does anyone know of any simple example or can point me in the right direction?

 

[GeForce]

AJAX would appear to be the perfect method of doing this.

When up/down is clicked, the JavaScript fires off the request to a PHP script on the server which updates the database, and returns a "successful" message, after which the JavaScript rearranges the items on the page.

 

[steroyle]

Its more the method of switching the order im trying to achieve.

I think i need to load the order into an array but can't get my head around how to switch the sort order.

[1]
[2]
[3]
[4]

If i want to move [2] up it would take two steps i think

Step 1

[2]
[2]
[3]
[4]

Step 2

[2]
[1]
[3]
[4]

But from Step 1 to Step 2 when I tell it to change [2] to [1] how does it know which [2] to change as there are two, or am I going about this the wrong way?

Hope that makes sense, I'm starting to confuse myself

 

[JonB]

You need to create a seperate temporary variable.

$varMove = $var1;
$var1 = $var2;
$var2 = $varMove;

 

[steroyle]

Would I still need to use an array or is there an easier way of doing it?

 

[JonB]

Ok, how do you store your data at the moment?

 

[philjohn]

Don't do the processing in PHP, that's what a database is for. Always process data as close to the source as possible.

This is a fairly easy thing to do, but you must be using a DB (or DB Engine) capable of transactions (so MS Sql Server, PostgreSQL or MySQL with an INNODB table)

Process is:

• Start transaction
• Get order_id of item you are moving
• Check that it won't be out of bounds moving it up or down (i.e. if it's currently 1 you cannot move it up, and if it's currently 4 in the example given you cannot move it down)
• Shift all of the order_id's lower, or higher (depending on direction), by 1
• Update the order_id of the moved item to the new value
• Commit transaction

This must be done with transactions otherwise you are leaving yourself open to race conditions if other users are also ordering things.

 

[steroyle]

I am storing my data in an sql database and got the following to kind of work.

$query = "UPDATE items SET order_id = '" . ($order_id - 1) . "' WHERE id = '" . $_GET['id'] . "'";	
mysql_query($query);
			
$query = "UPDATE items SET order_id = '" . ($order_id) . "' WHERE order_id = '" . ($order_id - 1) . "'";	
mysql_query($query);

I'm pretty sure this isn't the cleanest way to do it but there seems to be a problem with doing 2 updates straight after each other as when I run them separately it works.

I'll have a go at what you suggested philjohn. I struggle trying to work the process out in my head your list is helpful.

Also what do you mean when you refer to transaction?

 

[steroyle]

I'm still not having much luck. I have tried what you suggested philjohn but im not sure how to get all the order_id's change them +/- 1 then update database base.

Do I need to get the order_id's from the db and store them in an array made changes and then save array to database?

Starting to think I should have picked something simpilar to try

 

[steroyle]

Would anyone be able to help me with this im still having problems I've tried writting code a number of different ways but keep get the same problem.

First time I try to move an item up it works and the item above moves down one, but the second time I click it I get double order_id's and I can't get my head around the process im sure there is a much easier way.