Re: MySQL: Updating ID auto_increment fields
| From: | Andrey Hristov | Date: | Mon, 27 Aug 2001 14:29:51 +0000 |
| Subject: | Re: MySQL: Updating ID auto_increment fields | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-64639@lists.php.net to get a copy of this message | ||
It there are not many gaps, do it by hand. Another algorithm is to make a script:
<?php
db_connect();
$table = "some_table";
list($last_id)=mysql_fetch_array(mysql_query("select id from $table ORDER BY id DESC limit
1;"));
$counter=1;
while ($counter<$last_id){
$res=mysql_query("select * from $table where id=$counter;");
if (db_num_rows($res)){
}else{
while (!db_num_rows(db_exec("select count(*) from $table where id=$last_id;"))){
$last_id--;
}
db_exec("update $table set id=$counter where id=$last_id;");
$last_id--;
}
mysql_free_result($res);
$counter++;
}
I think that the idea is clear;
After this modification :
1)
create table $table."_new" select * from $table;
drop table $table;
->rename the $table."_new" to $table.
DO NOT FORGET TO CREATE AGAIN THE INDEXES ON THIS TABLE.
2)or create a empty table with the same structure. phpmyadmin is ideal for that. after that
'INSERT INTO table_new select
id,one,two, three,..., from table;
Hope this will help.
Andrey Hristov
IcyGEN Corporation
http://www.icygen.com
99%
----- Original Message -----
From: "Tom Churm" <thomas.churm@pixelpark.com>
To: <php-general@lists.php.net>
Sent: Monday, August 27, 2001 5:08 PM
Subject: [PHP] MySQL: Updating ID auto_increment fields
> howdy,
>
> this should be simple but i don't know it. i use the following mysql
> table field as the Key for my tables:
>
> "id int(10) unsigned NOT NULL auto_increment,"
>
> now, when i have to clean up items in my tables, like when users have
> sent off a .html form multiple times and i want to delete duplicate
> submissions, how can i quickly & easily update all of the 'id' fields in
> the entire table, to reflect the numeration change?
>
> now, using phpmyadmin, if i go in and delete a few entries, the id's
> have gaps in them..
>
> mucho gracias,
>
> tom
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net
> For additional commands, e-mail: php-general-help@lists.php.net
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>