Re: php mysql problem

Ross wrote:

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 ; -- snip --

Hi Ross, You can reuse item_id after they have been deleted. So when you delete item_id = 1, then next time you perform an insert, lookup the lowest free item_id number and give it as the item_id. That's one small solution. If you actually want to rank your records by more than just the item_id, then define a field for each kind of rank you want, or better, add it as a separate table, like: board_papers -----< rank

                    * id
                    * board_papers_fk <-- id from board_papers
                    * rank_type (example: readability, technicality...)
                    * rank_no (rank within the rank type)

Then you can order the board_papers after rank as you like! Just an idea :) Enjoy, John