This suggestion uses this table as an example. The hits field tracks the number of times a banner is used.
Code: Select all
DESCRIBE `banner`;
# +-------+-----------------+-------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+-----------------+-------+-----+---------+----------------+
# | id | int(3) unsigned | NO | PRI | | auto_increment |
# | name | varchar(15) | NO | | | |
# | hits | int(3) unsigned | NO | | 0 | |
# +-------+-----------------+-------+-----+---------+----------------+
SELECT * FROM `banner`;
# +----+-----------------+------+
# | id | name | hits |
# +----+-----------------+------+
# | 1 | Android World | 0 |
# | 2 | Banana Heaven | 0 |
# | 3 | Cold Infusions | 0 |
# | 4 | Doctor Electric | 0 |
# +----+-----------------+------+
If you can, create some views.* They aren't required. They just make things easier.
Create a view table to show the average number of hits for all banners.
Code: Select all
CREATE
VIEW
`banner_hits_avg` AS
SELECT
AVG(`b`.`hits`) AS `avg_hits`
FROM
`banner` AS `b`;
Create a view table to show one random banner that is behind or matching the average.
Code: Select all
CREATE
VIEW
`banner_random` AS
SELECT
`b`.`id` AS `id`,
`b`.`name` AS `name`,
`b`.`hits` AS `hits`
FROM
`banner` AS `b`,
`banner_hits_avg` AS `a`
WHERE
(`b`.`hits` <= `a`.`avg_hits`)
ORDER BY
RAND()
LIMIT
1;
When you are ready to grab a random banner, use this query to select the row from the banner_random view. Notice that the id field is stored in the @id variable so it can be used in an UPDATE query later.
Code: Select all
SELECT
`id`,
`name`,
`hits`,
@id := `id`
FROM
`banner_random`;
* If you don't have the ability to create views, you can combine the three SELECT queries. Put the query with AVG() in a subquery.
Immediately after selecting a random banner, update the banner table by incrementing the number of hits for the banner that was just selected.
Code: Select all
UPDATE
`banner`
SET
`hits` = `hits` + 1
WHERE
`id` = @id;
If you add a banner later, set its number of hits to the average, or set the hits for all banners to zero. Otherwise, that will be the only banner showing until its number of hits catches up to the average.