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 Beckman Systems Engineer, Fairfax Cable Access Corporation
beckman@purplecow.com http://www.purplecow.com/
---------------------------------------------------------------------------
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
