0

I am about to write an ad system and wanted to get some advice on a few things.

Basically my website will have several ad spots a page. For each ad spot there will be six ads that run in rotation.

I also need to track the number of views for these ads.

I am wondering how much of this I can combine query wise. My end goal would be to return a single ad row for each spot, and have the views column for the selected ads incremented.

My table will include...

id, ad_spot, view_count

If I query by the 'ad_spot' and total 'view_count' for the six rows returned, dividing by six and taking the remainder would tell me which of the ads should be used.

Is there any way to increment the view counts at the same time before returning to PHP?

How could I do the selection process in a query?

Would returning results for multiple ad_spots simply be a matter of doing a union of multiple queries?

Thanks for any advice!

christian
  • 2,279
  • 4
  • 31
  • 42

1 Answers1

0

Both could be achieved by creating a stored routine.

Use a stored procedure. While I don't have access to MySQL right now, the stored routine shouldn't be too far from

create function SelectAd() returns varchar(50) deterministic
begin
declare ad_id int;
declare ad_name varchar(50);
select id,name from test where id = (((select sum(viewcount) from test) MOD 6)+1) into ad_id,ad_name;
update test set viewcount=viewcount+1 where id=Lid;
return ad_name;
end

What you need the function/procedure to do is do the select and update. Using this function you would need only to select SelectAd() that would return the correct ad name (you could put an URL instead of a name ;) )

Enrico
  • 469
  • 3
  • 11
  • Thanks for the info! Never ventured into functions before. A couple things: I'd need to return the full rows b/c there is other info I'd need, link, image url, etc. Also the id field does not tell me which ad needs to be displayed, because there are several ad_spots in one table with six rows a piece. Thanks – christian Mar 22 '12 at 02:51