Can you update the row with the highest ID in one request?
I want to update the row with the highest ID. The problem is that I cannot find anything elegant solution for this. This is my best attempt:
$highestId = mysql_result(mysql_query('SELECT MAX(id) FROM stats'),0);
mysql_query("UPDATE stats SET views = views +1 WHERE id = $highestId");
Maybe there is a better approach than I think.
- I track the number of views, every day
- I want it to automatically increment the last (highest id) day
- In the evening, I run a cronjob that creates a new day.
Any suggestion on how to fix this issue is appreciated, even if it's a completely different approach.
Table stats => id | views
source to share
You can use the @ dev-null-dweller version if you don't suffer from table ordering. Or you can use a subquery.
UPDATE stats SET views = views +1 WHERE id = (SELECT * FROM (SELECT MAX(id) FROM stats) id)
You can profile both solutions and see which one works best for your case.
I think the following would be the best solution for you to keep track of stats does not require a cronjob.
Create a table with two columns
Table: stats
Columns: stat_date
(DATE) PRIMARY, views
(INT)
Then run the query:
$query = "INSERT INTO stats(stat_date, views) VALUES('".date('Y-m-d')."', 1) ".
'ON DUPLICATE KEY UPDATE views = views + 1';
Edit: I previously suggested a type DATETIME
for a column stat_date
, but obviously a DATETIME
doesn't make sense to you since you only want the day's record not the second. Thus, I replaced the type DATETIME
for DATE
.
source to share