Page 1 of 1

i want to randomly display my banners at equal times per day

Posted: Sun Nov 21, 2010 12:27 pm
by adsegzy
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

Re: i want to randomly display my banners at equal times per

Posted: Sun Nov 21, 2010 5:16 pm
by McInfo
Are you using a MySQL database?

Re: i want to randomly display my banners at equal times per

Posted: Mon Nov 22, 2010 5:10 am
by adsegzy
yes, i use mysql database and php code

Re: i want to randomly display my banners at equal times per

Posted: Mon Nov 22, 2010 1:16 pm
by McInfo
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.