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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

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

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post by McInfo »

Are you using a MySQL database?
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

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

Post by adsegzy »

yes, i use mysql database and php code
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Post Reply