banner rotation

I'm working on a banner rotation script for my new site but I'm not sure what would be the best way to rotate the banners in the pool. All the banners are stored in a database, I was thinking about adding an extra field to mark the last displayed banner (or the next one to be displayed) But since the users of the site can submit/delete their own banners this could cause a problem (when the marked record is deleted)
How would you implement the rotation of a banner pool?

Do you want to equally display the banners?

for starters, yes

Looks like I've nailed it: I'm counting the number of records in the banner pool and create a random number between 1 and the number of banners, thats the banner number that will be displayed...
Do you think this is a good way of doing this?

You could do this by using ORDER BY RAND(), but it still wont give the same impressions to every banner.

The best way is to add a field which will keep the count of impressions (eg. impr) and do something like :

SELECT id, .. .... . . .. .. . . . . . FROM banners ORDER BY impr ASC LIMIT 1

And then after you show the banner execute something like :

UPDATE LOW_PRIOTITY banners SET impr = impr + 1 WHERE id = $id LIMIT 1

Interesting. I will do that too.

Thanks :)

I'll try that, I already keeping a 'views counter' on the banners and I did notice that the random function does not show the different banners the same number of times, although it should in the long run...
A problem with your sollution would be that if the banners have been shown a lot of times already, a newly added banner will be shown exclusively untill its count is up to the same number, but I guess this can be solved by setting the counter to the same value as the other banners when adding a new banner.

Yes because otherwise the solution would be very costly to your server's CPU usage.

Except if you also use a php array on the session to know what banners you have shown to the user (so you want serve the same banner twice)