Basically I need to grab one banner from the database, to do this I calculate the weighting of all campaigns that are set to run on the current page, I call this in a subquery ordering the outter query by the banner weighting. The result of this is it picks the next campaign then picks the next banner within that campaign. A weighted round-robin..
Here is a stripped down version of the 300 line SQL I'm using..
Code: Select all
SELECT `id`
FROM `banners`
WHERE `campaign`
IN (
SELECT `id`
FROM `campaigns`
ORDER BY weight
limit 1
)
order by weight
limit 1
The problem with this is there could be a campaign for instance with no banners inside of it, in this case it would pick one campaign, then it would look for up to one banner inside that campaign. If it found none then there is no returned rows... If I remove the limit 1 inside my subquery I will get all the campaigns ordered correctly, but the outter query will pick the first banner that falls into one of these campaigns. Is there an easy way to tell mysql to go through my IN values in order?
Solution (but not as 'clean' as I had hoped it'd be)
Code: Select all
$result = mysql_query('SELECT `id` from `campaigns` order by `weight`');
while(list($id)=mysql_fetch_row($result)) {
$banner_result=mysql_query('select `id` from `banners` where `campaign` = '.(int)$id);
if (mysql_num_rows($banner_result)) {
$banner = mysql_result($banner_result,0,0);
break;
}
}
mysql_free_result($result);
mysql_free_result($banner_result);