[SOLVED] Help with modifying 'position'

[rondog]

I have a list of assets that are loading from my database. When I select the data, I do ORDER BY position ASC so it lists them as 1,2,3,4 etc.

 

I have up and down arrows next to each list item so they can rearrange the list. Whats the best way to go about reordering the items?

 

I was thinking if they hit move up, select the position of the previous item and then just swap the values. Same goes for hitting move down, except I would select the position of the next item. Is their an easier way or is that pretty much how you do it?

[socratesone]

You're attempting to re-order them in PHP or JavaScript? I'm guessing you're doing the reordering on the client side and storing it via ajax or form submission?

[rondog]

No I am reordering them in PHP. I found this query for swapping data between two rows which might work, but I dont know how to use it:

 

mysql_query("UPDATE table SET x = y, y = @temp WHERE (@temp := x) IS NOT NULL");

 

This application is actually being built in flash and I am using AMFPHP to do the database interaction.

[rondog]

Since I cant figure out that query above I am trying this:

function moveItemUp($id)
{
	$query = mysql_query("SELECT proj_id,position FROM projectData WHERE id = '$id'");
	$row = mysql_fetch_array($query);
	$currentPosition = $row['position'];
	$projectID = $row['proj_id'];
	$destination = $currentPosition - 1;
	$destination2 = $currentPosition;

	$query = mysql_query("UPDATE projectData SET position = '$destination' WHERE id = '$id' AND proj_id = '$projectID'");//this item moved up
	$query2 = mysql_query("UPDATE projectData SET position = '$destination2' WHERE id = 'the id being swapped' AND proj_id = 'the proj_id being swapped'");//this item moved down

}

 

I know thats not correct. My mind is boggled right now by this logic for some reason. I cant think of how to do this...

[.josh]

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

[rondog]

Ahh thank you violent. I have it kind of working now. It is swapping, but they seem to just swap back and forth. It will just keep doing 3,2 - 2,3 - 3,2 - 2,3 etc regardless if I keep moving it down or up.  My table is a bit more complex than the example used which is where my confusion is coming from. This is how I have my function setup:

function swapPosition($dir,$id,$projID)
{
	 switch ($dir)
	 {
		  // if we're going up, swap is 1 less than id
		  case 'up': 
			 // make sure that there's a row above to swap
			 $swap = ($id > 1)? $id-- : 1;
			 break;
		  // if we're going down, swap is 1 more than id
		  case 'down':
			 // find out what the highest row is
			 $sql = "SELECT count(*) FROM projectData WHERE proj_id = '$projID'";
			 $result = mysql_query($sql) or die(mysql_error());
			 $r = mysql_fetch_row($result);
			 $max = $r[0];
			 // make sure that there's a row below to swap with
			 $swap = ($id < $max)? $id++ : $max;
			 break;
		  default:
			 $swap = $id;
	  }
	   $sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap)";
	   $result = mysql_query($sql) or die(mysql_error());
}

 

and then a screenshot of my current table setup:

 

Can you see where I am going wrong??

 

 

[.josh]

I see multiple rows with the same position number.  Each row has to have its own position number (they have to be unique, but don't make the column a unique type or the swap won't work (read the tutorial for details))

[rondog]

I am aware of the multiple position values which is why I have the proj_id field. I will only be manipulating one project at a time so if I could somehow modify this query to only look at the current project id, it should work. I just dont know how.

 

It has to be something in this line..

		   $sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap)";

[.josh]

okay you are going to have to add on a condition to the end of the query that restricts it to the current project.  Assuming that $projID is correlated with proj_id ...

 

$sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap) AND WHERE proj_id = '$projID'";

[rondog]

Thats giving me an error, yet I dont see it...hmmm

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE proj_id = '1'' at line 1'"

 

Thanks for the help so far

[premiso]

$sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap) AND proj_id = '$projID'";

 

You do not need the extra where you had. One where should suffice.

[.josh]

haha oops. yeah take out that WHERE.  should just be AND proj_id = '$projID'

[rondog]

$sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap) AND proj_id = '$projID'";

 

You do not need the extra where you had. One where should suffice.

 

Ahh ok that fixed it and it is only changing the selected project id

 

Unfortunately, I still have 1 minor issue:

 

The ordering just keeps going back and forth. Say I try and move 2 down, 2 moves down and 3 moves up which is correct.

 

If I hit move down on 'new' 2(at position 3 now) it goes back to 2 and 3 goes back to where it was originally at as well. Does that makes sense?

[.josh]

I just tested the query string directly in my db as you have it setup (filtered by proj_id) and it works perfectly.  I looked at your code above and it looks exactly like the tutorial (except for the proj_id filters in the queries), so... you sure you're passing the right vars to your function? echo them out inside your function to make sure.

[rondog]

I am testing this in AMFPHP since its going to be run through flash eventually, but when I return the values I am inputting this:

"Initial vals: down 4 1 > Outputted vals: down 5 1"

 

I input:

 

direction: down

id: 4

projID: 1

 

and I get that above every time and id 4 and 5 just swap back and forth.

 

So if I input down,4,1 - the new position will be 5. If I enter that again, the new position should be 6. The Ids are never changing just the position field which I believe is correct.

 

Here is my whole function:

function swapPosition($dir,$id,$projID)
{	
	$initialVals = "$dir "."$id "."$projID";
	 switch ($dir)
	 {
		  case 'up': 
			 $swap = ($id > 1)? $id-- : 1;
			 break;
		  case 'down':
			 $sql = "SELECT count(*) FROM projectData WHERE proj_id = '$projID'";
			 $result = mysql_query($sql) or die(mysql_error());
			 $r = mysql_fetch_row($result);
			 $max = $r[0];
			 $swap = ($id < $max)? $id++ : $max;
			 break;
		  default:
			 $swap = $id;
	  }
	   $sql = "UPDATE projectData SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap) AND proj_id = '$projID'";
	   $result = mysql_query($sql) or die(mysql_error());
	   return "Initial vals: ".$initialVals." > Outputted vals: $dir "."$id "."$projID" ;
}

 

maybe its because $swap is basing itself off of $id?

[.josh]

$swap is supposed to be based off $id.  It takes the id of the item you are clicking up or down on, and uses $id-1 for up, $id+1 for down, checking to make sure there is something to swap.

 

Look: http://www.crayonviolent.com/usort.php

 

That is an example of what it's supposed to do.  So yes, if you keep clicking the exact same button, they will keep swapping back and forth.

[rondog]

Well thats the problem then because I dont want the swap value based off of the $id. I need it based off of the position field

[.josh]

okay I think maybe you're misunderstanding or making this more complicated than it really is or something. Or else I'm just not understanding your goal here...

 

You need a column to hold a number for each row.  The script is written to where the numbers have to be 1-n, incremented by 1.  So for instance, if you have 5 rows, the column you are using to keep track of the custom sort order has to be like this:

 

position

1

2

3

4

5

 

That's what your position column is.  But what you have done is something like this:

 

proj_id position

1        1

1        2

1        3

1        4

1        5

2        1

2        2

3        1

3        2

3        3

 

And that is fine, because in your queries you are filtering position by proj_id, so when you are actually performing the swap, you are only looking at for instance (with proj_id 1):

 

proj_id position

1        1

1        2

1        3

1        4

1        5

 

So overall, the swap is being based off both proj_id and position.  So in the code that increments/decrements the swap and performs the queries. The $id being passed to your function needs to be what is in the position column for that row. 

[rondog]

Crayon, thank you very much for your help. That last reply set things straight for me. I kept passing my actual ID, but when I passed my position variable it worked as expected

[.josh]

groovy