[SOLVED] recursive -1 on position field

[rondog]

I have a list of items that a user can order by clicking 'move up' / 'move down'. These items are in my DB and when SELECTing I ORDER BY position. 'Position' is a field name that just go 1 through X.

 

The user also has the ability to delete an item by them clicking 'delete' on the specific item.

 

Say a user deletes Item 5 out of 10.

 

I will now have a gap in my position field - [1,2,3,4,6,7,8,9,10]

 

How can I ensure that when the users delete's an item, all items after it move their position number down 1 as well?

[Daniel0]

Select them and recalculate their position values according to their order.

 

SELECT id FROM foo WHERE bar='something' ORDER BY position ASC;

 

Now loop through them. Start with position=1 and increment it on each iteration. Then save to database.

[Mark Baker]

UPDATE table
   SET position=position-1
WHERE id > $deletedItemID

[Daniel0]

Heh... that'll work too

[rondog]

thanks guys. I'll give that a try

[rondog]

Is there a problem with my query? Its returning false and just deleting the row:

<?php
function deleteProjectItem($id,$projID)
{
	$query = mysql_query("DELETE FROM projectData WHERE id = '$id' AND proj_id = '$projID'");
	$renum = mysql_query("UPDATE projectData SET position = 'position-1' WHERE id > '$id' AND projID = '$projID'");
	if ($renum)
	{
		return true;
	}
	else
	{
		return false;
	}
}
?>

[Daniel0]

'position-1' is a string literal whereas position-1 is the field name called position subtracted by 1.

[rondog]

Ahh ok gotcha..I had that before and it was still giving me false..I guess it would help to name my field correctly. I had:

 

AND projID = '$projID'

 

should have been:

 

AND proj_id = '$projID'

 

It is working now thanks!