Re: [PHP-DB] Sorting in numerical order, and then randomly
Hi Peter,
Thanks for the suggestion, suggestion 3 works perfectly if I also switch
the order of the numbering as you suggest, because then I can do order by
priority desc, rand(), which returns the numbers first and then the 0's,
which is what I really wanted. Also, good point about adding higher
priority advertisers - I hadn't thought of that.
Thanks again,
-Lisi
At 02:54 AM 11/18/02 -0500, Peter Beckman wrote:
Your options, as I see them, with 3 being the best I could come up with:
1. Make two queries. Depending on how many rows returned, this may be
the less taxing option processor wise.
2. Make the random query. As you iterate through them push folks with
priority 0 on one stack, priority 1 another stack, etc. At the end
of it all you'll have a bunch of stacks with a randomly ordered list
of advertisers.
3. (This is the obvious winner)
select * from ads_value where status='current' order by priority
asc, rand()
This will return 0's first, 1,2,3... etc after that in a random
order. I might recommend re-ordering your priority making 100 the
highest and 1 the lowest priority above 0. This way if you get an
advertiser that beats everyone out, they can be 101 or 150 or (if
you are lucky in this market) 2000. If you do it your way (as I
understand it) you will have to bump down #1 to #2, #2 to #3, etc in
order to put a newer higher priority advertiser first.
Peter
On Sun, 17 Nov 2002, Lisi wrote:
I am using MySQL to store ad information in the following table:
CREATE TABLE IF NOT EXISTS ads_value (
img_link varchar(50),
text text,
service varchar(50) default NULL,
title varchar(50) default NULL,
priority int(2) default '0',
status enum('current', 'old'),
ID int(3) NOT NULL auto_increment,
PRIMARY KEY (ID)
) TYPE=MyISAM;
Ads for which advertisers pay more will have a higher priority - i.e. 1, 2,
3, etc. Everything else will have a priority of 0. When the page loads, I
want to first display any ads that have a priority higher than 0 to be
displayed in order, and then the remaining ads with a priority if 0 to be
displayed in random order. They have to be displayed in a different order
each time, so that each ad has the same chance of being displayed in a
particular spot as any other ad. The only spots a random ad cannot be in is
one taken by a higher paying ad. I hope this is clear.
Is it possible to do this with one query? Or would I have to use 2
different queries:
select * from ads_value where status = 'current' and priority 0 order by
priority asc
and then
select * from ads_value where status = 'current' and priority = 0 order by
RAND()
TIA,
-Lisi
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
---
Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED] http://www.purplecow.com/
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Sorting in numerical order, and then randomly
I am using MySQL to store ad information in the following table:
CREATE TABLE IF NOT EXISTS ads_value (
img_link varchar(50),
text text,
service varchar(50) default NULL,
title varchar(50) default NULL,
priority int(2) default '0',
status enum('current', 'old'),
ID int(3) NOT NULL auto_increment,
PRIMARY KEY (ID)
) TYPE=MyISAM;
Ads for which advertisers pay more will have a higher priority - i.e. 1, 2,
3, etc. Everything else will have a priority of 0. When the page loads, I
want to first display any ads that have a priority higher than 0 to be
displayed in order, and then the remaining ads with a priority if 0 to be
displayed in random order. They have to be displayed in a different order
each time, so that each ad has the same chance of being displayed in a
particular spot as any other ad. The only spots a random ad cannot be in is
one taken by a higher paying ad. I hope this is clear.
Is it possible to do this with one query? Or would I have to use 2
different queries:
select * from ads_value where status = 'current' and priority 0 order by
priority asc
and then
select * from ads_value where status = 'current' and priority = 0 order by
RAND()
TIA,
-Lisi
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Sorting in numerical order, and then randomly
Your options, as I see them, with 3 being the best I could come up with:
1. Make two queries. Depending on how many rows returned, this may be
the less taxing option processor wise.
2. Make the random query. As you iterate through them push folks with
priority 0 on one stack, priority 1 another stack, etc. At the end
of it all you'll have a bunch of stacks with a randomly ordered list
of advertisers.
3. (This is the obvious winner)
select * from ads_value where status='current' order by priority asc, rand()
This will return 0's first, 1,2,3... etc after that in a random
order. I might recommend re-ordering your priority making 100 the
highest and 1 the lowest priority above 0. This way if you get an
advertiser that beats everyone out, they can be 101 or 150 or (if
you are lucky in this market) 2000. If you do it your way (as I
understand it) you will have to bump down #1 to #2, #2 to #3, etc in
order to put a newer higher priority advertiser first.
Peter
On Sun, 17 Nov 2002, Lisi wrote:
I am using MySQL to store ad information in the following table:
CREATE TABLE IF NOT EXISTS ads_value (
img_link varchar(50),
text text,
service varchar(50) default NULL,
title varchar(50) default NULL,
priority int(2) default '0',
status enum('current', 'old'),
ID int(3) NOT NULL auto_increment,
PRIMARY KEY (ID)
) TYPE=MyISAM;
Ads for which advertisers pay more will have a higher priority - i.e. 1, 2,
3, etc. Everything else will have a priority of 0. When the page loads, I
want to first display any ads that have a priority higher than 0 to be
displayed in order, and then the remaining ads with a priority if 0 to be
displayed in random order. They have to be displayed in a different order
each time, so that each ad has the same chance of being displayed in a
particular spot as any other ad. The only spots a random ad cannot be in is
one taken by a higher paying ad. I hope this is clear.
Is it possible to do this with one query? Or would I have to use 2
different queries:
select * from ads_value where status = 'current' and priority 0 order by
priority asc
and then
select * from ads_value where status = 'current' and priority = 0 order by
RAND()
TIA,
-Lisi
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
---
Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED] http://www.purplecow.com/
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
