Re: [PHP] Pragmatically changing a "Record Number"

Stut wrote:
Jason Pruim wrote:
Hi Everyone,

Hi Dr Jason.

I think after I get this question answered, I can stop asking for awhile since my project will be done, at least until the users say "What happened to XYZ????" then I'll ask again :)

I asked on a MySQL list about "Resetting a auto increment filed" so that there arn't any gaps in the record number.

So to say it another way, I have a table that has 900 records in it, I've added 3 records, but then deleted 2 of those which puts the actual record count at 901 but my auto increment field starts at 904 on the next insert.

Is there away with PHP that I can pragmatically change that value to the total records in the database more so then a representation of the actual record number?

What are you actually trying to achieve? Why do you need all records to have a sequential number? Ignore how you're going to do it, just tell us why you think you need this, because I've never come across a reason to need this.

-Stut
I've seen many people who wished to "fix" sequences like this before, usually because they believe there is something "wrong" with there being gaps in there. However, from a database-point-of-view an auto_increment value represents a unique row which _stays_ unique. As such because it doesn't re-assign values used before you keep database integrity intact because old possible links between rows/tables won't be reused and thus won't form unintended links (ie. say you delete row [id=2] from table a, which was linked via [id=2] to a row in table b. If it was reused, a "fresh" row would suddenly inherit its predecessors links (which it should not!).

The auto_increment value represents just that, an internal unique id for a row in a specific table. It doesn't represent the location of a row in respect to other rows (ie the 2nd, 3rd and 4th of the table), simply because that's now what it's intended for. If that is what you wish, you'll need to find a different way of achieving it.
.

Relevant Pages

  • Re: [PHP] Pragmatically changing a "Record Number"
    ... So to say it another way, I have a table that has 900 records in it, I've added 3 records, but then deleted 2 of those which puts the actual record count at 901 but my auto increment field starts at 904 on the next insert. ... Is there away with PHP that I can pragmatically change that value to the total records in the database more so then a representation of the actual record number? ... I've seen many people who wished to "fix" sequences like this before, usually because they believe there is something "wrong" with there being gaps in there. ...
    (php.general)
  • Re: help with perl dbi and update without locks
    ... If I understand correctly you want to auto increment a numeric primary key ... This is heavily dependent on the database you are using. ... Another common method is to use an insert trigger that calculates the next ...
    (comp.lang.perl.misc)
  • Re: How can you create a field that is concatenated?
    ... want gaps translates to "never expose AutoNumbers to users because they can have ... Are you saying you think this makes the Autonumbers suitable to expose ... centralized database but which are generated by a few hundred external ones. ... but if I am communicating with an external user it is ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Want to write your SQL statements and even stored procedures in pure C#?
    ... >> In sync with the database? ... Firebird is closer to Oracle. ... identity fields are the item that is poor. ... sequences you too have to grab the new sequence value. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Want to write your SQL statements and even stored procedures in pure C#?
    ... I'll be writing more articles soon. ... > your reasoning is that you claim your solution is truly database ... SQL server, DB2, and Firebird. ... Second of all - sequences are not horrible, identity fields are the item that is poor. ...
    (microsoft.public.dotnet.framework.adonet)