I am building a Banner Ad System using MYSQL/PHP in which i will be collecting statistical data.
so far my system is designed to firstly ensure that every banner is displayed at least once.
my next feat would be to display a random banner based on a ratio of some of of expression that deals with the number of displays or clicks
My problem is im not sure of a proper strategy to be devised.
the following is my table structure
banner_images
ID|filename|alt|active
banner_stats
ID|displays|clicks|refererurl|dateshown
So far i have the following query
Code: Select all
SELECT banner.ID, banner.file, banner.description, banner.active, stat.displays FROM banner_images AS banner, banner_stats AS stat GROUP BY banner.ID HAVING stat.displays <= AVG(stat.displays)) AND banner.active = 'Y' ORDER BY RAND() LIMIT 1so i have this for now
Code: Select all
SELECT banner.ID, banner.file, banner.description, banner.active, stat.displays FROM banner_images AS banner, banner_stats AS stat GROUP BY banner.ID HAVING stat.displays <= MAX(stat.displays) AND banner.active = 'Y' ORDER BY RAND() LIMIT 1Kendall