How to update a counter for a resultset

Question

Hi, i'm creating something similar to an advertising system. I would like to show, for example, 5 ads (5 record) from a given database table. So i execute something like

SELECT * FROM mytable 
ORDER BY view_counter ASC
LIMIT 5

ok, it works. But, how can contextualy update the "view_counter" (that is a counter with the number of show) maybe with a single SQL ? And, if i don't ask too much, is it possible to save the "position" which my record are returned ? For example, my sql return

- record F   (pos. 1)
- record X   (pos. 2)
- record Z   (pos. 3) 

And save in a field "Avarage_Position" the .. avarage of position ?

Thanks in advance.

Regards

Answer 1

You could do something like this, but it is pretty ugly and I would not recommend it (see below for my actual suggestion about how to handle this issue).
Create a dummy_field tinyint field, sum_position int field and average_position decimal field and run the following few statements within the same connection (I am usually very much against MySQL stored procedures, but in this case it could be useful to store this in a SP).

SET @updated_ads := '';
SET @current_position := 0;
UPDATE mytable SET view_counter= view_counter+1, 
    dummy_field = (SELECT @updated_ads := CONCAT(@updated_ads,id,"\t",ad_text,"\r\n"))*0, /* I added *0 for saving it as tinyint in dummy_field */
    sum_position = sum_position + (@current_position := @current_position +1),
    average_position = sum_position / view_counter
ORDER BY view_counter DESC
LIMIT 5;
SELECT @updated_ads;

Then parse the result string in your code using the delimiters you used (I used \r\n as a row delimiter and \t as the field delimiter).

What I actually suggest you to do is:

• Query for selected ads.
• Write a log file with the selected ads and positions.
• Write a job to process the log file and update view_counter, average_position and sum_position fields in batch.

Answer 2

how can contextualy update the "view_counter" (that is a counter with the number of show) maybe with a single SQL ?

That's usually something handled by analytic/rank/windowing functions, which MySQL doesn't currently support. But you can use the following query to get the output you want:

  SELECT *,
         @rownum := @rownum + 1 AS rank
    FROM mytable 
    JOIN (SELECT @rownum := 1) r
ORDER BY view_counter ASC
   LIMIT 5

You'd get output like:

description  |  rank
--------------------------
record F     |  1
record X     |  2
record Z     |  3

---

if i don't ask too much, is it possible to save the "position" which my record are returned ?

I don't recommend doing this, because it means the data needs to be updated every time there's a change. On other databases I'd recommend using a view so the calculation is made only when the view is used, but MySQL doesn't support variable use in views.

There is an alternative means of getting the rank value using a subselect - this link is for SQL Server, but there's nothing in the solution that is SQL Server specific.

Answer 3

Hi guys, thanks for your answer. I solved simply executing the same SELECT query (with exactly the clause WHERE, Order BY and LIMIT) but, instead SELECT, i used UPDATE. Yes, there's an "overhead", but it's simple solution.