Posted by Boris Stumm on 02/16/07 14:10

ZMAN wrote:
> CREATE TABLE coupons (
> id int(11) NOT NULL auto_increment,
> data longtext NOT NULL,
> display_coupon varchar(10) NOT NULL default '',
> position tinyint(20) NOT NULL default '0',
> usetemp tinyint(10) NOT NULL default '0',
> KEY id (id)
> ) TYPE=MyISAM;
[...]
> They have asked me to allow them to be able to place the order in which
> they appear on the html page.
> I created a position field for this, SELECT ..etc... ORDER BY position.
>
> So, for example there are 4 coupons.
> If they change coupon in position 3 to be in position 2, how do I renumber
> all the position fields in order.
> I can't for the life of me figure out how to do this with a sql call.

Lets say your coupon in question has the ID 4711, and is moved from position
X to position Y.

if x > y:
update coupons set position = position + 1
where position between Y and X and id <> 4711;
if x < y:
update coupons set position = position - 1
where position between X and Y and id <> 4711;

something like that.

[Back to original message]