Update the auto increment value

[danishbacker]

Is it possible to update the AUTO_INCREMENT value who's attributes are primary key, not null, auto increment and also an INT type

The thing is I need to synchronize the 'image_id' to access images sequentially
and if one row is deleted the sequence is destroyed Help me!

<?php
include('db_conexn.php');
$data = mysql_query("SELECT image_id FROM imagetable")
or die(mysql_error()); 
while($info = mysql_fetch_array( $data )) {
	echo $id += 1;
	echo " - ".$info['image_id']; 
	$sync=mysql_query("UPDATE imagetable SET image_id = $id");
	echo "<BR>";
}
echo " - <BR> " . $id +=1;
$autoinc = mysql_query("ALTER TABLE imagetable AUTO_INCREMENT = $id");
?>

[dickersonka]

I would suggest it is bad practice to use the image_id for sequence. Add an int column to use for sequence, update it if necessary.
You shouldn't need to update it, if you use order by with sequence column, this would be a much cleaner way, than updating an id column.

[danishbacker]

if a row is deleted the sequence will be destroyed can you suggest me any way to update the id.
i've done using following code

$id=1;
$count = mysql_num_rows( mysql_query("SELECT image_id FROM imagetable") )
$data = mysql_query("SELECT * FROM imagetable ORDER BY image_path ASC")
or die(mysql_error());
while($info = mysql_fetch_array($data)) {
$sync = mysql_query("UPDATE imagetable SET image_id = $id ");
}

this is not working ie all ids have same number
it all contains the last value if $id
help me

[mike_g]

It seems that you are not increment the id maybe add an increment to your loop? IE:

$id=1;
$count = mysql_num_rows( mysql_query("SELECT image_id FROM imagetable") )
$data = mysql_query("SELECT * FROM imagetable ORDER BY image_path ASC")
or die(mysql_error());
while($info = mysql_fetch_array($data)) 
{
    $sync = mysql_query("UPDATE imagetable SET image_id = $id ");
    $id++;
}

[danishbacker]

i have updated $id but still the problem persists
the loop is execute completely on a row and only after that it goes to the next row
this is why every row has same number
help me solve this

[danishbacker]

$data = mysql_query("SELECT id ,name FROM imagetable")
or die(mysql_error()); 
$id=0;
while($info = mysql_fetch_array( $data )) {
$id=$id+1;
mysql_query("UPDATE imagetable SET id =$id  where name='$info[1]'");
}

instead of using auto increment i incremented the value manually and
updated each fields.