Re: Pragmatically changing a "Record Number"

On 8/29/07, Stephen <stephen-d@rogers.com> wrote: > --- Jason Pruim <japruim@raoset.com> 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 > >