Page 1 of 1

Displaying Banners via Analysis Expression

Posted: Tue Feb 10, 2004 7:34 am
by kendall
Hello,

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 1
this i found doesnt work to well

so 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 1
However i find this abit simple minded and would like a more "variety" way of working out a formula to get a random banner based on the display or clicks statistics....Can you guys offer me some suggestions?

Kendall