Ad:

MySQL Discussion Thread
Marked Solved
Views: 16570

Aug 21st, 2008

Update the auto increment value

Expand Post »

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!

MySQL Syntax (Toggle Plain Text)
<?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");
?>
<?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");
?>

Similar Threads

MYSQL: update all fields in a table (increase value by one) in PHP
display image from databse in MySQL
Please check my code below in Java
Not getting information from the database in PHP
Fatal error: require once in PHP

danishbacker

Reputation Points: 10

Solved Threads: 7

Junior Poster in Training

Offline

97 posts
since Apr 2008

Permalink

Aug 22nd, 2008

Re: Update the auto increment value

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.

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 22nd, 2008

Re: Update the auto increment value

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

MySQL Syntax (Toggle Plain Text)
$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=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

danishbacker

Reputation Points: 10

Solved Threads: 7

Junior Poster in Training

Offline

97 posts
since Apr 2008

Permalink

Aug 22nd, 2008

Re: Update the auto increment value

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

(Toggle Plain Text)

$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++;
}

Last edited by mike_g; Aug 22nd, 2008 at 5:59 pm.

mike_g

Reputation Points: 12

Solved Threads: 3

Newbie Poster

Offline

14 posts
since Jul 2008

Permalink

Aug 23rd, 2008

Re: Update the auto increment value

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

Last edited by danishbacker; Aug 23rd, 2008 at 4:59 am.

danishbacker

Reputation Points: 10

Solved Threads: 7

Junior Poster in Training

Offline

97 posts
since Apr 2008

Permalink

Dec 15th, 2008

Re: Update the auto increment value

php Syntax (Toggle Plain Text)
$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]'");
}
$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.

Last edited by danishbacker; Dec 15th, 2008 at 5:01 am.

danishbacker

Reputation Points: 10

Solved Threads: 7

Junior Poster in Training

Offline

97 posts
since Apr 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: qick question

Next Thread in MySQL Forum Timeline: How select Documents which contain more than n tag-specified words

DaniWeb Message

Cancel Changes

User Name
Password