From: Peter Brawley
Date: May 2 2006 3:08pm
Subject: Re: sorting with php/mysql
List-Archive: http://lists.mysql.com/mysql/197512
Message-Id: <44577603.5090709@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ross,
> This is my database.
Nope, it's a table, not a database.
> ..I will use the item_id for the order but what if I want to change 
> item_id 3 to item id 1?
A primary key shouldn't be edited, and the literal value of an 
auto_increment column oughtn't to be relevant. If you really need a 
settable ordering column, add it to the table, and if you really need 
sequencing numbers to have no sequence breaks, consider disallowing 
deletion in favour of carrying a column you can use for marking rows 
'inactive' or whatever.
> How can I push all the items down one place? How can I delete any gaps 
> when items are deleted. Say I delete item 2 how can I delted the gap 
> and 'promote item_id 3 to item_id 2
This question conflates data maintenance with reporting. If deletion of 
a row required closing up the row-number sequence below it, such 
deletion would require updating all rows with id values greater than the 
id of the deleted row. That would be unmanageable. To report rows as 
1,2,3 without breaks, you just need to write something like ...

  SET @ord=0
  SELECT @ord:=@ord+1 AS Item, doc_date AS Date,... &c ...

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


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 5/1/2006