UPDATE Field, change sequential number

[RIRedinPA]

I have a database with the following fields:

 

id, groupname, magcode, magname, grouprank - id is primary key and auto incremented, grouprank is an int, everything else varchar

 

This is for users of a magazine production schedule to group the items in their schedule (Department 1, Feature 1, etc.) I'm building an admin page so they can add or take away groups. I'm having a problem resorting the grouprank field after an item has been deleted. (Rule: Each item has to have a unique grouprank - 1, 2, 3, 4, etc.)

 

I'm using the following code: ($thisid and $magcode are passed in using AJAX)

 

//get vars
$thisid = $_GET['thisid'];
$magcode = $_GET['magcode'];

//get group rank
$query = "SELECT grouprank FROM groups WHERE id = $thisid";
$result = adminQuery($query); 
$itemcount = mysql_num_rows($result); 
$i=0;
if ($itemcount > 0) { 
while ($itemcount > $i) { 
	$i++;
	$grouprank = mysql_result($result, $i-1, 'grouprank');
}
}

//delete group 
$query = "DELETE FROM groups WHERE id = " . $thisid; 
$result = adminQuery($query); 

//resort
if ($grouprank == 1) { 
$query = "SELECT * FROM groups WHERE magcode = '$magcode'";
$result = adminQuery($query);
$itemcount = mysql_num_rows($result);
$y=0; 
if ($itemcount > 0) { 
while($itemcount > $y) { 
	$y++;
	$thisid = mysql_result($result, $y-1, 'id');
	$grouprank = mysql_result($result, $y-1, 'grouprank');
	$newgrouprank = $grouprank - 1; 

	$updatequery =  "UPDATE groups SET grouprank = $newgrouprank WHERE magcode = '$magcode' AND grouprank = $grouprank";
	echo $updatequery . "\n"; 
	//$updateresults = adminQuery($updatequery);
}
}
} else { 
//code
}

 

the problem is I keep ending up with all the groups having a grouprank of 1. But if I check the query they look right. In this example there were 5 items in the database, with groupranks of 1-5. I deleted the item with grouprank 1, the queries during the while loop were:

 

UPDATE groups SET grouprank = 1 WHERE magcode = 'AA' AND grouprank = 2

UPDATE groups SET grouprank = 2 WHERE magcode = 'AA' AND grouprank = 3

UPDATE groups SET grouprank = 3 WHERE magcode = 'AA' AND grouprank = 4

UPDATE groups SET grouprank = 4 WHERE magcode = 'AA' AND grouprank = 5

 

So why does everything end up with a grouprank of 1? Is this a case of the db not refreshing? Thanks!

[fenway]

This comes up over an over again -- you either need to keep track of what moved, or just re-order them.