Hello friends, am having some advert banners to this play on my site. i have written the script that will randomly select the banner, but the problem is that at the end of the day some banners are displayed more that others eg. at the end of a day, a banner might have been displayed 300 times while another will just be displayed 70 times. Pls what can i do to randomly select the banners but at the end of each day, all the banners will have been displayed at the number of time or the difference in the number of times displayed will not be much. code/syntax for this will be appreciated.
regards
i want to randomly display my banners at equal times per day
Moderator: General Moderators
Re: i want to randomly display my banners at equal times per
Are you using a MySQL database?
Re: i want to randomly display my banners at equal times per
yes, i use mysql database and php code
Re: i want to randomly display my banners at equal times per
This suggestion uses this table as an example. The hits field tracks the number of times a banner is used.
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.
Create a view table to show one random banner that is behind or matching the average.
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.
* 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.
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.
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 |
# +----+-----------------+------+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`;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;Code: Select all
SELECT
`id`,
`name`,
`hits`,
@id := `id`
FROM
`banner_random`;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;