I have a table with the following columns (simplified)
id, city, spot, views, clicks, url
'spot' is an identifier for the location of the ad, the other fields are pretty self explanatory.
I have the following stored procedure which currently passes in the city and the spot and pulls the next ad for that spot and city if one exists. It does so by totaling the ad views for the given city/spot and dividing by the total number of rows for the given city/spot. The remainder then tells which ad should be shown. This is determined by creating a new enumerated row.
DROP PROCEDURE IF EXISTS get_ad //
CREATE PROCEDURE get_ad(in paramcity VARCHAR(30), IN paramspot VARCHAR(30))
SELECT * FROM (
SELECT @t1 := @t1 + 1 AS num, ads.*
FROM ads, (SELECT @t1 := -1) init
WHERE city = paramcity AND spot = paramspot
ORDER BY id ASC
WHERE dt.num = (
SELECT SUM(views) % COUNT(id) FROM ads
WHERE city = paramcity AND spot = paramspot);
I am struggling with a couple things.
1. I need to update the views column for the row returned to make the whole thing work. I am uncertain how to do this while also returning the row.
2. I'd like to be able to supply and single city and multiple spots, possibility in a delimited string, and return/update multiple rows with a single call. Is there any way to take advantage of the city remaining the same performance wise?
I truly appreciate any help or direction you can provide.