Re: php mysql problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux