Mysql - 每天投票一次

Question

I have an entry where users can vote once per day. I''m saving this in my database.
Now I need to check, if the user is allowed to vote on this entry again (after one day).

So far I got this:

SELECT count(*) 
FROM entries e
WHERE e.voterID =1
AND e.pID =1
AND e.date < NOW( ) - INTERVAL 1 
DAY   

But this doesn't work to well, when in the DB there are more entries for the voter and pid. A voter can vote multiples times for the same entry.
if there are more entries for the same user and the same projects, count(*) fives me a value more then 1. etc.

How do I check if the user is allowed to vote again properly?
Thanks.

Answer 1

Did the present user vote for the present pID in the most recent 24 hours? If so, the votecount result in this query will be more than zero.

 SELECT count(*) AS votecount
   FROM entries AS e
  WHERE e.voterID = 1
    AND e.pID = 1
    AND e.date >= NOW() - INTERVAL 1 DAY

Notice the >= comparison for the date. Your sample code says <.

You will need a compound index on (voterID, pID, date) when you need this query to run efficiently on a large table.