php mysql help

Hello.

i have an arcade page where people can earn points each day by playing, these points are reseted, by this code

$sqlReset = "UPDATE users SET today_points=0;

PHP:

, but before this i want an sql query that gives the one with the most points that day a point my my database, and now i wonder how i can do this ?

basiclly, the query i want should update the row "stars" to 1 for the user with the most point in the row "today_points" in the table "users"

i hope that someone can help me as i dont know to to make the query check for the user with the most points.

 

$sql = "SELECT username_or_id, today_points FROM users ORDER BY today_points DESC LIMIT 1"

PHP:

that way you can get the user with the most points. You can also use max(today_points) I believe.

 

The select suggested by Mukelo will get the userid of the top daily points you then simply update that user with the result so you end up with something like this...

$sql = mysql_query("SELECT username_or_id FROM users ORDER BY today_points DESC LIMIT 1");
if (mysql_num_rows($sql)) > 0) {
$result = mysql_fetch_array($sql);
$update_user = mysql_query("update users set stars = stars + 1 where userid = " . $result["username_or_id"] . " limit 1");
}

PHP:

That will find the user with the most daily points and add +1 to their star count - I'm assuming you want to add to the star count?

Once you have updated the user run the update...

$sqlReset = "UPDATE users SET today_points=0; 

PHP:

 

Why use two statements when you can use one

$sqlReset="UPDATE users b 
LEFT JOIN 
 	(SELECT MAX(today_points) highscore FROM users) ms 
 	ON b.today_points=ms.highscore 
SET b.stars = CASE 
 		WHEN ms.highscore IS NOT NULL THEN b.stars+1
		ELSE b.stars
		END,
b.today_points = 0";

PHP:

 

thanks! question though... if starts is already set to 1, will it update it so it becomes 2, 3, 4, etc?

 

Yes, it will always increase the value by 1 so if it is already 1 then it will become 2 and so on.

 

awsome, thank you very very much Gawk!