Re: php mysql problem

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