mysql + PHP voting system with ranking

Question

This is the current system. When a user votes on an item his IP and the POST_ID are recorded into the IP_LOG table. The problem is I wish to order posts by their rank (Upvotes-Downvotes).

So the display is something like .

1. POST_ID -> 32425 (7 upvotes)
2. POST_ID -> 12331 (4 upvotes)
3. POST_ID -> 24211 (3 upvotes)

This is simple enough the problem becomes when an item in the list moves up or down, I wish to allow any user who voted on this item to be able to re-vote on that item.

So if item 3 were to receive 2 up-votes it would switch place with item 2, then I would remove from the IP log table all IP's that voted for either 12331 or 24211.

1. POST_ID -> 32425 (7 upvotes)
2. POST_ID -> 24211 (5 upvotes)
3. POST_ID -> 12331 (4 upvotes)

Table IP_LOG

ID  |     IP          | POST_ID | 
---------------------------------
1   |  xxx.xxx.xx     |  12331  |  
---------------------------------
2   |  yyy.yyy.yy     |  12331  |

Table POST_RANK

ID  | POST_ID | Downvotes | Upvotes
-----------------------------------
1   |  32425  |  3        |  10
-----------------------------------
2   |  12331  |  1        |  5
-----------------------------------
3   |  24211  |  1        |  4

My question is this:

What would be my SQL query that allows me to select the rank of the POST_ID based on the amount of (upvotes-downvotes)

something that returns :

1. POST_ID => 32425, Rank => 1
2. POST_ID => 12331, Rank => 2
3. POST_ID => 24211, Rank => 3

attempt :

SELECT COUNT(*)
          FROM POST_RANK
         WHERE Upvotes - Downvotes) AS Rank

Alternatively what would be another way to accomplish this functionality without using a cron job. I want it updated immediately.

Answer 1

Try this:

SELECT (Upvotes - Downvotes) as Rank
          FROM POST_RANK
         WHERE post_id=<id>

Update:

Try this:

select @rownum:=@rownum+1 as rank, p.* FROM POST_RANK, (SELECT @rownum:=0) r order by (Upvotes - Downvotes) desc