Re: php mysql problem

This is my database now...I will use the item_id for the order but what if I want to change item_id 3 to item id 1? How can I push all the items down one place? How can I delete any gaps when items are deleted. CREATE TABLE board_papers ( id int(4) NOT NULL auto_increment, doc_date varchar(10) NOT NULL default '0000-00-00', article_type enum('agenda','minutes','paper') NOT NULL default 'agenda', fileName varchar(50) NOT NULL default '', fileSize int(4) NOT NULL default '0', fileType varchar(50) NOT NULL default '', content blob NOT NULL, item_id int(10) default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; ""Ross"" <ross@aztechost.com> wrote in message news:6C.5F.05123.70D27544@pb1.pair.com... > > 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 > > (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.