Looping through columns

simey

Hey there

I'm in a bit of trouble here, seeing as I don't know how to loop through columns and update their values.

I am making a small script to delete values from a table. Every entry in the table has an "ID" and an "URL" tag. The problem when I delete an entry, though, is that the ID's do not update, and the result is this:

http://img443.imageshack.us/img443/3518/examplej.jpg

So I have created a while loop, which should update the ID-values (decrement all the values of ID by 1 after the deleted entry for every row).

It doesn't do that, however. And I'm not sure what I'm doing wrong.

Here is the code:

$id = $_POST['acceptid'];

$con = mysql_connect("localhost","root","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("urllist", $con);

$result = mysql_query("SELECT * FROM urllist WHERE ID='$id'");

$row = mysql_fetch_array($result);

$voreslink = $row['url'];

mysql_query("INSERT into linkpool (url) VALUES('$voreslink')");

mysql_query("DELETE FROM urllist WHERE ID='$id'");

(int)$id++;

while ($id = $row)
	{
		(int)$newID = (int)$id-1;
		mysql_query("UPDATE urllist SET ID = '$newID' WHERE ID = '$ID'");
		(int)$id++;
	}

I know the code is a bit long, but it's just in case you need to know the rest.
The $id is sent from another PHP-page, and contains the ID-number to be deleted. 1,2,3 etc.

Thanks in advice

kkobashi

If your ID's are auto_increment they won't reupdate and adjust to be in sequential order with no gaps in between (i.e. if 1, 2, 3, 4 IDs exist and you delete the third one it will be 1, 2, 4 - not 1, 2, 3)..

Also, you can never assume the next autoincrement value is going to be the next logical value (i.e. if 1, 2, 3 are created you cannot assume next number is 4).

In your code, I noticed that you don't test for existence. That is, you have to check via mysql_num_rows that the select statement found a row with ID.

$result = mysql_query("SELECT * FROM urllist WHERE ID='$id'");
if (mysql_num_rows ( $result ) <= 0)
  return;
.
.

bradgrafelman

You should never update the AUTO_INCREMENT values in a table when you delete a row... that defeats the purpose for using it to begin with.

If you want a counter variable, just create one in your PHP scripts when you retrieve information and display it from the database.

Tonata

Is that not a good thing, I mean an ID is an just an ID you are not suppose to change it, It should remain intact, always. take humans for example, what if we inherited IDs upon someone's death, would that be a good thing, wouldn't it be difficult to find someone by ID who constantly keep changing their IDs. I urge you to use the counter in your PHP script, to count number of rows returned and display. if there are 3 rows with ID 1, 2, 4 respectively, counter will output 1, 2, 3 associatively. and instead of printing the ID, you print the counter. (Loud and Clear)