[PHP-DB] Re: remove from db renumbering

[Jason Morehouse]

That's the way auto_increment works.  You'll have to make the field
an int (no auto_increment) and supply a record id yourself on insert.

On Mon, 01 Jul 2002 08:21:18 +1200, Chris Payne wrote:

 Hi there everyone,
  
 I have my id field auto incrementing (Of course) and I have a utility
 that allows me to remove items from it, the problem is when I remove
 something the items after it keep their ID’s leaving a gap in the ID.
 For example, say I have 1-10 in the ID field and I remove number 8, I’ll
 have 1-7 and then 9-10, what I need is for 9-10 to go down to 8-9, how
 can I do this?  It’s really important as I have an ad-rotator system
 which gets the total number of fields from the DB and if there is an
 empty ID it causes there to be an invalid image appear (Of course).
  
 So all I need is to renumber the ID field after a delete I guess, but
 how?
  
 Any help would be extremely grateful :-)
  
 Thanks
  
 Chris
  
 
 ---
 Outgoing mail is certified Virus Free.
 Checked by AVG anti-virus system (http://www.grisoft.com).
 Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002


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

[PHP-DB] Re: remove from db renumbering

[Adam Royle]

You should not rely on the autonumber as a sorting method. You should use it
as a reference. I see you have two easy options (there may be more, but this
seems suitable).

1. Get the SQL to pull a random record (requires mySQL 3.23.x or higher)
SELECT * FROM tblName ORDER BY RAND() LIMIT 1

2. Get all of the ID numbers into an array and pick a random value, then
retrieve the rest of the details using the row ID you picked from the array.

Adam


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