RE: [PHP] Banner Ad Script

I have written a target based model that would work equally well for weighted that uses one query that returns one row to select the next ad and one query to update the count (two queries per ad view). I use it to serve sites that have over 3 million page views per week with 4-5 ads per page. The entire ad server is highly featured and the query looks really complicated so I am going to try to give you a simplistic view of how it works. Table: ad id, url, end_date, impression, impression_goal, hourly_count, hourly_target To select ad: SELECT id, url, (hourly_count / hourly_target) AS percent FROM ad ORDER BY percent asc LIMIT 1 This selects the ad that still needs the greatest percentage of hits to reach its goal. Update ad: UPDATE ad SET hourly_count=hourly_count+1, impression=impression+1 where id=$ad_id This updates both the total impressions and the hourly count. Cron script: Every hour I run a cron script that resets the hourly_count to 0 and re-calculates the hourly_target based on how many ads need to be served per hour for the remaining life of the ad. This creates automatic balancing to hit target goal. If the site is not oversubscribed, I have found the impression == impression_goal exactly by the time the ad has ended if you ad to the select ad query not to include ads with percentage >=1. mockup cron script: $hours_rem = (end_date - now()) / 3600 $impressions_left = impression_goal - impression $hourly_target = $impression_left / $hours_rem UPDATE ad SET hourly_count=0, hourly_target=$hourly_target WHERE id=$id There ya have it. You just need to add in campaigns, overages, priority, available hours for the ad, categories, page targeting, exclusivity, etc... I hope this conveys the basic theory of working with percentages instead of true randomness. I believe you will find it quite effective of creating the illusion of randomness. A couple of reference sites that use this is type of ad serving are http://www.kgw.com, http://www.wwltv.com. By the way, all content on these pages is dynamically created using PHP and MySQL. Here is the actual query for this site: SELECT ad.id, ad.url, ad.category, ad.alt, ad.border, filter.id as filter_id, (daily_count / daily_target) AS percent FROM ad LEFT JOIN filter ON ad.id = filter.ad_id LEFT JOIN campaign ON ad.campaign_id = campaign.id WHERE ad.site_id=$SITE_ID AND ad.width=$WIDTH AND ad.height=$HEIGHT AND ad.status=1 AND ((hour(now()) >= ad.start_hour and hour(now()) <= ad.end_hour) OR (ad.end_hour < ad.start_hour and (hour(now()) <= ad.end_hour OR hour(now()) >= ad.start_hour))) AND ((ad.daily_count / ad.daily_target) < 1. OR ad.overage = 1) AND ad.id NOT IN ($AD_EXCLUDE) AND ad.category NOT IN ($CATEGORY_EXCLUDE) AND campaign.status = 1 AND campaign.start_date <= unix_timestamp() AND campaign.end_date >= unix_timestamp() AND filter.ad_group IS NULL OR filter.ad_group = '' OR filter.ad_group = '$AD_GROUP') AND (filter.page IS NULL OR filter.page = '' OR filter.page = '$REQUEST_URI') AND (filter.location IS NULL OR filter.location = 0 OR filter.location = $LOCATION_ID) ORDER BY priority desc, exclusive desc, location desc, page desc, ad_group desc, percent asc limit 1 Hope this helps you and maybe others :) John Hamlik Solid Technology, Inc. http://www.solidtechnology.com > -----Original Message----- > From: Andrew [mailto:fisherres@msn.com] > Sent: Tuesday, July 11, 2000 8:19 PM > To: php-general@lists.php.net > Subject: Re: [PHP] Banner Ad Script > > > Thanks for replying, but I'm afraid this isn't going to > work without > hanging every now and then. Do you have any suggestions on > how I can select > random banner ad(s) based on weight w/ one being the lowest? > > Currently, I'm using the following algorithm which works > great if you > only have one banner ad: What I have done in the past is > place each banner > into an array n number of times, where n is the banner > weight. This way > banners with higher weight appear in the array more times > than banners with > a lower weight, and thus have a higher chance of being > selected. Note that > when I have done this, I only store the index to the banner > (unique ID) > within the array. > > Andrew > digi-FX > ------------------------------- > http://www.digi-FX.net > ----- Original Message ----- > From: Andrew <fisherres@msn.com> > To: <php-general@lists.php.net> > Sent: Tuesday, July 11, 2000 4:50 PM > Subject: [PHP] Banner Ad Script >