Re: php mysql problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At 11:00 AM +0100 5/2/06, Ross wrote:
Just say I have a db

CREATE TABLE `mytable` (
  `id` int(4) NOT NULL auto_increment,
`fileName` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


when I add items they go id 1,2,3 etc. Whn I delete them gaps appear. 1, 3,
7. I need to know

(a) when items are removed how can I sort the database to all the gaps are
take out 1, 3, 7 becomes 1,2,3

Ross:

I am sure that the advice of everyone here and on any mysql list will be NOT to do what you are asking. You're looking at the ID as if the order of that field means something, it shouldn't.

However, to provide you with what you asked for, please review:

/* Renumber the Existing Sequence
alter table mytable
drop id,
add id int unsigned not null auto_increment,
auto_increment = 1;

Keep in mind that by doing this, your dB should remain flat -- you cannot successfully renumber a relational dB without also considering all the dependant tables, which is not trivial.

HTH's more than it hurts.

tedd

--
--------------------------------------------------------------------------------
http://sperling.com

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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux