MySQL旋转广告系统，存储过程还是php处理？

Question

I am writing a mysql/php ad system

I have an ads table with the following columns

id, spot, city, state, img, url, views, clicks

Each ad "spot" can have up to 6 rows for each "city/state" combo

Each page template has approximately 15 unique ad "spots"

On page load I need to grab the next ad in rotation for each of the "spots" in the template and increment their "views"

What I have tried thus far...

I figured to reduce database calls I would use a stored procedure, inserted below ("state" not yet implemented).

This procedure works by totaling views for all rows matching a given "spot" in a given "city/state" combo, dividing the total by the number of matching rows and using the remainder to determine the row to return.

I am wondering if this is the right approach. It's definitely not the fastest, and "command out of sync" issues arise with other database functionality after I call it using mysqli, despite using "query" method.

I am wondering about alternatives.

If I do the processing in PHP and increment after the fact, I assume I would need to ditch the "remainder " selection method and go with a less desired "random" selection method to avoid collision.

Would it be wise to do the following

1. Add a "page" column
2. Return all possible ads for the "page" within a "city/state" combo to PHP. ** Would be 6 times more than would be displayed.
3. Randomly select which ads to display via PHP
4. Update those selected rows via an UPDATE with a WHERE id IN () clause

Any other suggestions?

DROP PROCEDURE IF EXISTS get_ads //
CREATE PROCEDURE get_ads(in paramcity VARCHAR(30), IN paramspots VARCHAR(255))
BEGIN

DECLARE s VARCHAR(50);
DECLARE spots VARCHAR(255);
DECLARE p INT(10);
DECLARE l INT(10);

SET s = '';
SET spots = paramspots;
SET p = 0;
SET l = 0;

CREATE TEMPORARY TABLE output (id INT(10) NOT NULL, spot VARCHAR(50) NOT NULL, url VARCHAR(255), image VARCHAR(255));

REPEAT

  SET p = LOCATE(',',spots);

  IF  p > 0 THEN
    SET s = SUBSTRING(spots,1,(p - 1));
    SET spots = SUBSTRING(spots, (p + 1));
    SET l = CHAR_LENGTH(spots);
  ELSE
    SET s = spots;
    SET spots = '';
    SET l = 0;
  END IF;

  INSERT INTO output SELECT (@t2 := dt.id) AS id, spot, url, image FROM (
    SELECT @t1 := @t1 + 1 AS num, ads.*
    FROM ads, (SELECT @t1 := -1) init
    WHERE city = paramcity AND spot = s
    ORDER BY id ASC
  ) dt
  WHERE dt.num = (
  SELECT SUM(views) % COUNT(id) FROM ads
  WHERE city = paramcity AND spot = s);

UPDATE ads SET views = views + 1 WHERE id = @t2;


UNTIL l <= 0 END REPEAT;

SELECT * FROM output;

DROP TEMPORARY TABLE output;

END //