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

+3


source to share


3 answers


Yes:



UPDATE stats SET views = views +1 ORDER BY id DESC LIMIT 1

      

+7


source


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

.

+4


source


You can also sort the results by id desc and just edit the first result.

Edit: Too late sorry. :)

+1


source







All Articles