for loop to re-index data

[squiblo]

I am making a script to delete images from user albums, each photo has a value to show what place in the album they come, when I delete an image the order will change and may look like this "1, 2, 3, 7, 8...." to I am trying to change this so the order does not change and will always increase by one every time. "1, 2, 3, 4, 5......".

 

Here is what I have tried....

//count how many photos are remaining in the album
$query = mysql_query("SELECT recordListingID FROM photos WHERE usr_id='{$uid}' AND album='{$aid}' ORDER BY recordListingID ASC");
$photoCount = mysql_num_rows($query);
$row = mysql_fetch_assoc($query);
//if there are still images remaining update recordListingID
if ($photoCount != 0)
{
	for($i=1;$i>=$photoCount;$i++)
	{
		$recordListingID[$i] = $row['recordListingID'];
		$update_listing = mysql_query("UPDATE photos SET recordListingID='{$i}' WHERE recordListingID='{$recordListingID[$i]}' AND usr_id='{$uid}' AND album='{$aid}'");
	}
}
else
exit();

 

Thanks

[kenrbnsn]

You didn't say what the problem is.

 

Try this instead:

<?php
$q = "SELECT recordListingID FROM photos WHERE usr_id='$uid' AND album='$aid' ORDER BY recordListingID ASC";
$query = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
$i = 0;
while ($row = mysql_fetch_assoc($query) {
     $uq = "UPDATE photos SET recordListingID='$i' WHERE recordListingID='{$recordListingID[$i]}'";
     $update_listing = mysql_query($uq) or die("Problem with the update query: $uq<br>" . mysql_error());
     $i++;
}
?>

 

Ken