auto-increment problem

[leerocker56]

hey,
ive added a bunch of entries to my table, and then deleted some that i dont want, but now new entries' userID's wont auto increment to the actual number of rows i had, it still includes the ones i deleted. any idea how to fix that?

thanks.
-lee

 

[spookie]

I dont think u can manipulate that by MySQL.
U have to write some server side script to fix that.

Get the total number of records in a table.
use a for loop and run an update query inside that.
in php it will be something like

$query = "select count(*) from tablename" ;
$result = mysql_query($query) ;
list($count) = mysql_num_rows($result) ;
$min = 0 ;
for ($i=1;$i<=$count;$i++) {
$max=$min+1 ;
$getidquery = &quot;select id from tablename LIMIT $min,$max&quot; ;
$getidresult = mysql_query($getidquery) ;
list($id) = mysql_fetch_array($$getidresult) ;
$updatequery = &quot;update tablename set id = $i where id = $id&quot; ;
...
$min++ ;

the code is not tested but u got the idea


--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[nigelcohen]

Another cludge, but one which works well, is to avoid deleting the records, but instead update the records so they can be identified as inactive. (eg. Add a column to the table called something like "IsActive integer default 1" and set a record to 0 when inactive.)

This requires adding a "where" clause of something like "where IsActive=1" whenever you want to display/extract active records.

One advantage of this cludge is that you avoid deleting information which you may subsequently want. There are several disadvantages, of course, such as retaining redundant data and apparent loss of sequence when looking at active records in isolation. We use this system, and it works very well for us.