0

I have a two MYSQL tables:

Table-1

id      catid                   title                   user_rating
123     8                       title-one               3
321     8                       title-two               5

and

Table-2

listing_id      title                   user_rating
123             title-one               3
321             title-two               5

Plus, I have this query that calculates the current rank of each "title" based on "user_rating".

 SELECT 
    MAX(x.rank) AS rank
 FROM
    (SELECT 
        a.id,
            a.catid,
            a.title,
            b.listing_id,
            @rank:=@rank + 1 AS rank
    FROM
        `table-1` a
    INNER JOIN `table-2` b ON a.id = b.listing_id
    CROSS JOIN (SELECT @rank:=0) r
    WHERE
        catid = '8'
    ORDER BY user_rating DESC) x
WHERE
    id = 123

Now, my issue: I want to calculate the difference in "ranking" (rank) when I update the "user_rating" value.

Please, note: the "user_rating" value is updated by a php script that allow users to vote for a specific content (range 1 to 5, step 0.5).

What's the best way to get the difference between the "previous rank" and "current rank" after the update?

Thanks in advance to all.

Melon
  • 874
  • 8
  • 17
David Madhatter
  • 285
  • 2
  • 6
  • 13
  • 2
    You'd have to store the "old" rank somewhere separately from the new rank. Once you've done the `update` query, the original rank field's value will have been replaced. You also can't "select" data from an update, so there's no way to do it in a single step. so `UPDATE yourtable SET oldrank=rank, rank=$newrank`, then `SELECT oldrank-rank` is probably your best bet. – Marc B Jan 13 '14 at 14:20
  • 1
    you can also create a log table and populate it with triggers on row_update with the old rank, the new rank, and the diference. – Melon Jan 13 '14 at 14:27
  • Sorry for the delay, I was traveling. Thanks ti Marc B and Melon for the inputs. I think that store "oldrank" and "new rank" in a separated table is the best solution. Many thanks – David Madhatter Jan 14 '14 at 09:24

0 Answers0