On Tue, May 2, 2006 5:00 am, Ross wrote: > 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
You do *NOT* want to do this on an auto_increment field in a relational database. It's just a Bad Idea in so many ways... For starters, you're going to need that 'id' field in a bunch of other tables to relate the two tables -- That's kinda why they call it a RELATIONAL database. So if you re-number this table, you have to go through all the other tables related to this table and update them as well. You're looking at a cascading nightmare of updates. > (b) allow the ids to be changed so the items can change position in > the > list. If I change id 3 to id 1 then everything else shifts down. > > If anyone has seen the amazon dvd rental list where you can swap dvd > to the > top of the list and delete this is what I am trying to achive with > php/mysql. THAT is another kettle of fish entirely. You pretty much just need to manage the numbering on a different column "by hand" create table mytable(id int(11) auto_increment, rank int(4)); Queries you will find useful: //The rank for the new item added to end of list: select max(rank) + 1 from mytable; //Queries to move to top of list: $id = /* get ID of row here */ $rank = /* get current $rank of that row here */ update mytable set rank = rank + 1 where rank < $rank update mytable set rank = 1 where id = $id It's easy to mess up and get everything "off by one" but this is not rocket science to figure out and debug. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
