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

On 8/29/07, Stephen <stephen-d@xxxxxxxxxx> wrote:
--- Jason Pruim <japruim@xxxxxxxxxx> wrote:

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?

Some database concepts:

The autoincrement feature is to provide a unique "key"
for the record. It does not provide an "order". Many
tables have more than one "order".

Usually a different field or field determines the
order(s). It usually has an index.

To provide a row number, based on some order, you need
a field for this. Whenever a field is deleted, you
would need to repopulate the fields in each record
after the deleted record in the database.

I saw a nested SQL query that did this once, but my
SQL is not good enough to try to illustrate.

Fairly easy - depending on your version of MySQL - if you are 5.0+,
then the following will do it:

SELECT @rownum:=@rownum+1 rownum, t.* FROM (SELECT @rownum:=0) r, myTable t;

(Quoted from a post by Mark Malakanov on April 30 2006 1:42pm at
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html)

-James



Stephen

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


.

Relevant Pages