Updating item numbers (or IDs)

[cover]

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?

[Erwin Moller]

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?

I hope not.
Why do you want such a strange thing?

While designing a database, good designers make use of foreign keys to to
make sure the relations in the database will stay consistent.

Consider the following pseudocode:

create table tbluser(
userid autonumber Primary Key,
username text
);

create table writtenarticles(
writtenarticleid autonumber Primary Key,
writtenby numeric references tbluser(userid),
title text,
article text
);

The above situation makes sure that the value for writtenby in
tblwrittenarticles exists in column userid in table userid.

If you decide to change the numbers in tbluser, this relation will be broken
(and fires a exception/error in most cases.)

However, in some databases you have the option to cascade a change through
all related tables via their foreign keys.

Bottomline: Such an autonumber/serial field is ment to uniquely point to a
certain row, which is handy in a relational database.
It is not ment as a counter...

If you need such a counter, just do it programmatically, like looping over
your resultset (ordered by userid eg), and increase your own counter.

I don't want to lecture you, but I think what you asking for is conceptually
wrong. :-)

Good luck.

Regards,
Erwin Moller

[Andy Jeffries]

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

[cover]

On Thu, 27 Apr 2006 08:53:33 GMT, Andy Jeffries
<ne**@andyjeffries.co.uk> wrote:

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...

Thanks guys - appreciate the informative replies.

[Gleep]

Try to avoid situations where you need and autonumber id field to be sequencially perfect. However
you might be dealing with an odd situation.

I found that the best thing to do. Is to do a table backup. You don't have to back up the entire db
just the table in question.

first do a full backup of your table.
then do a data export of the same table. WHen exporting data I like to use the ~ as the separator
because the , may be contained in some data fields and scews up the field separation.
next open up that exported data in excel. Go to tools > import text file make sure to tell excell
the separator is ~
you will see all your data - hopefully the primary id autoincrement is the very first column. Do a
sort on that column adn then delete that column, save that file
now you delete or drop table from your db.
Then open up the saved exported table in a word processor and copy the sql that build the table
structure, copy and paste that code into the SQL section of phpadmin and go, this will recreate the
structure of the table.
Next go to the bottom of that page and you will see, insert text file, select that and you are
taken to a page/form to upload data. Make to set ~ as the separator and lower on the page select the
checkbox that says "local" or the upload sometimes won't work.
Then your data is reinserted and autonumberd starting from 0.

Note: if you have a huge amount of data in the data text file, you can only upload 2 megs at a time.
In situations like that, use you editor to split the file in half and upload each section.

This process is easy to do, sounds much worse that it really is, However there is potential for
disaster. Try practising on a home server first. get it down. If all else fails at least you have a
full and complete back up of the data and you can dump that table back and get back to where you
started.

On Wed, 26 Apr 2006 22:24:23 -0700, cover <co****************@yahoo.com> 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?

[Jerry Stuckle]

Gleep wrote:

Try to avoid situations where you need and autonumber id field to be sequencially perfect. However
you might be dealing with an odd situation.

I found that the best thing to do. Is to do a table backup. You don't have to back up the entire db
just the table in question.

first do a full backup of your table.
then do a data export of the same table. WHen exporting data I like to use the ~ as the separator
because the , may be contained in some data fields and scews up the field separation.
next open up that exported data in excel. Go to tools > import text file make sure to tell excell
the separator is ~
you will see all your data - hopefully the primary id autoincrement is the very first column. Do a
sort on that column adn then delete that column, save that file
now you delete or drop table from your db.
Then open up the saved exported table in a word processor and copy the sql that build the table
structure, copy and paste that code into the SQL section of phpadmin and go, this will recreate the
structure of the table.
Next go to the bottom of that page and you will see, insert text file, select that and you are
taken to a page/form to upload data. Make to set ~ as the separator and lower on the page select the
checkbox that says "local" or the upload sometimes won't work.
Then your data is reinserted and autonumberd starting from 0.

Note: if you have a huge amount of data in the data text file, you can only upload 2 megs at a time.
In situations like that, use you editor to split the file in half and upload each section.

This process is easy to do, sounds much worse that it really is, However there is potential for
disaster. Try practising on a home server first. get it down. If all else fails at least you have a
full and complete back up of the data and you can dump that table back and get back to where you
started.

On Wed, 26 Apr 2006 22:24:23 -0700, cover <co****************@yahoo.com> 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?

This procedure doesn't work if you have foreign keys. You need to change those,
also.

And yes, having holes in the sequence should not be a problem.

BTW - this should be in comp.databases.mysql - it's not a PHP question.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================