Posted by J.O. Aho on 02/11/07 11:38

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;
>
> I have an auto dealership website I do.
> This table allows them to add or remove dealership discount coupons as
> needed. They do this through an admin form
> I built for them.
> 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.

I don't see any easy way to do this, but was thinking of a portal which used
"weight" on elements, the heavier the lower down. This method does of course
give a risk of testing. This of course give the advantage that you don't have
to renumber anything. You could have a drop down menu from where they selects
weights, this way you limit the options they have, for more fine tuning you
could add an option of "add one more weight".
Adding a good sorting is a good thing to do, first use the weight column ASC
and second use the id as sorting, as I guess they will like to have newer
coupons higher up by default, so use DECS (you should ask if there is a
criteria that makes a coupon to get higher up in a list, most of the times,
use that as secondary sort).


--

//Aho

[Back to original message]