[SOLVED] Need help for updating id column for a deleted row

[5kyy8lu3]

Heya.  I have a problem.  The page I'm working on does pagination of my pictures.  When I delete a picture (aka delete a row from the table), the "id" column is missing a number.  So if i delete picture 4, the numbering goes 1 2 3 5 6 7...... it's missing 4, and that sucks because I use total rows minus current ID to print the "current picture: " number onto the screen.

 

so... is there a way to re-write the id column from top to bottom from 1 to NumRows in sequential order? or any other way you can think of me working around this?  if i had 500 pictures, it just seems improbable to do 500 queries just to fix the numbering

 

i'm stumped, any ideas would be great, thanks

[mmarif4u]

One idea for you:

make another field say its del in that table. when you delete a pic. put 1 and X there. by default should be empty. So all the rows will be there. Dont delete any row. but you have to change your script of showing pictures, just to add this, del = '' in your query to not show the deleted one.

 

Hope it helps.

[5kyy8lu3]

One idea for you:

make another field say its del in that table. when you delete a pic. put 1 and X there. by default should be empty. So all the rows will be there. Dont delete any row. but you have to change your script of showing pictures, just to add this, del = '' in your query to not show the deleted one.

 

Hope it helps.

 

heh i was just about to try that actually but i found something else that worked

 

I selected the id column and put it into an array, then i did a while loop to go through the array one row at a time, and i put an if statement inside the loop that checked for the array's current id value against the current id of the picture on that page, and if it did match it saved my loop counter into a variable, and that variable tells me the #, here's the code for if anyone ever searches this:

 

$query5 = "SELECT id FROM " . $PicDir . " ORDER BY id ASC";
$result5 = mysqli_query($cxn, $query5);
$loopcount = "0";
$NumInTable = "0";
while ($row = mysqli_fetch_array($result5))

{
	$loopcount = $loopcount + 1;
	if ( $row['0'] == $CurrentId )

		{
			$NumInTable = $loopcount;
		}

}

echo 'Picture ', $NumInTable, ' of ', $NumRows;