renumbering fields

am 10.02.2007 22:33:57 von ZMan

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.

Any help would be greatly appreciated.
Thanks in advance!

ZMAN

Re: renumbering fields

am 10.02.2007 22:47:46 von Paul Lautman

Re: renumbering fields

am 11.02.2007 11:52:07 von leader

On Sat, 10 Feb 2007 21:47:46 -0000, "Paul Lautman"
wrote:

>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.
>>
>> Any help would be greatly appreciated.
>> Thanks in advance!
>>
>> ZMAN
>
>Here's a whizzy way to make it drag'n'drop with Ajax
>http://www.phpriot.com/d/articles/client-side/sortable-list s-with-php-and-ajax/index.html
>
I do this all the time.
Create a field in your database to contain a number (integer) and then
SELECT * from $TableName ORDER BY number.
When they do enter new (or revised) data, they can choose the new
number which will thus decree the order of display.
It's best if you tell them to use widely-separated numbers; I tell all
my people to use units of thousands, 1000, 2000, 3000, etc. That way,
inserting new units is a piece of cake.
A script to delete the old numbered record then takes care of
business.
HTH.

Re: renumbering fields

am 11.02.2007 12:38:55 von Shion

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

Re: renumbering fields

am 16.02.2007 15:10:34 von Boris Stumm