Re: Updating item numbers (or IDs)

On Wed, 26 Apr 2006 22:24:23 -0700, cover wrote:
> After a while of deleting records in a MySQL db, there gets to be the gaps
> in the id numbering system. i.e. 1, 2, 3, 6, 7, 12, and so on. Is there a
> way to renumber the id system in a table for 1, 2, 3, 4, 5, 6, etc without
> manually going in and changing those numbers?

While I generally agree with Erwin on this, it's probably best leave them
alone, I do appreciate there may be a reason for it so...

The only way to do it AFAIK in MySQL (pre version 5 at least) is to loop.
There's no "non-manual" way. In pseudo-code:

$handle1 = open_mysql_connection_and_db();
$handle2 = open_mysql_connection_and_db();

$result1 = send_mysql_query("SELECT ID FROM Table1", $handle1)
$ID=1;
while ($row = get_mysql_row($result1)) {
        if ($row[ID] != $ID) {
                send_mysql_query("UPDATE Table1 SET ID='$ID' WHERE ID='$row[ID]'",
                        $handle2);
        }
        $ID++;
}

It's not pleasant and you'll need to trigger it after deleting a record.

I also use pseudo-code above, partially because I use my own database
abstraction object and can't remember off-hand what the proper mysql
functions are (and am too busy to look them up at the minute) and
partially because most people also use some form of database abstraction
object (PDO etc) so you'd need to rewrite it to fit in with that anyway.

I also have classes generated for tables that have events for beforeDelete
and afterDelete so I'd use an afterDelete event to compact the ID numbers.

But, as I said at the start, be very aware of Erwin's comments they are
likely true so be sure you want to do this before doing it.

Cheers,

Andy

-- 
Andy Jeffries MBCS CITP ZCE   | gPHPEdit Lead Developer
http://www.gphpedit.org       | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos