SQL query - How to display points in the last 24 hours?

I'm building a website with points system, similar to Stackoverflow;

This is the code I'm using to display top users (for all time):

SELEC开发者_开发问答T id, username, active, points 
FROM users 
WHERE active='1' 
ORDER BY points DESC

But how can I show the top users in 24 hours (also points)?

You would have to make a special table that would record when (and how many) points the user received.

EDIT

E.g. table points

user_id   time         awarded_points
-------------------------------------
1         1298745681   10
...

Then you just ask for the points awarded today with WHERE time > UNIX_TIMESTAMP(CURDATE()) or in the last 24 hours WHERE time > UNIX_TIMESTAMP()-24*3600.

Example use:

SELECT u.id, u.username, u.active, SUM(p.awarded_points) AS points
FROM points AS p
LEFT JOIN users AS u ON u.id = p.user_id
WHERE time > UNIX_TIMESTAMP(CURDATE())
AND u.active = '1'
GROUP BY p.user_id
ORDER BY points DESC

I'd also suggest adding a LIMIT to the query, especially if your site grows big.

Your need another table, where all users activites/points would be write
And than join with this table, and condition to 24h

Add a table recording the points each user receives and when with a foreign key relation to the users table

For example a table named points with the following columns

• userid (FK to users table)
• datetimeallocated
• numberofpoints
• other columns of interest e.g. user who allocated the points etc

You then need to query this table e.g.

SELECT SUM(numberofpoints), userid
FROM points
WHERE datetimeallocated >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY userid
ORDER BY SUM(numberofpoints) DESC