Take the 2-minute tour ×

I want to be able to update a row with the highest ID. The problem is: I can't find any elegant solution to do this. This is my best attempt so far:

$highestId = mysql_result(mysql_query('SELECT MAX(id) FROM stats'),0);

mysql_query("UPDATE stats SET views = views +1 WHERE id = $highestId");

Maybe there there is a better approach than I am thinking of.

  • I am tracking the amount of views, every day
  • I want it to auto-increment the last (highest id) day
  • In the evening I'm running a cronjob that creates a new day.

Any suggestion on how to tackle this problem are welcome, even if it is a whole different approach.

Table stats => id | views

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted


UPDATE stats SET views = views +1 ORDER BY id DESC LIMIT 1
share|improve this answer
Thanks for your quick response! Question: And what about working with DATE columns? Or is this more efficient? I want it to be as lightweight as possible. –  SuperSpy Mar 18 '12 at 10:11
I would say go with the solution that best suits your goal, the difference in fetching a date or a int value wont make any noticable difference. Specially when its just 1 query a day. :) –  hustlerinc Mar 18 '12 at 10:26
The above query will be run about 10 000 times a day. (also not that much ;) ) Oke, anyway. –  SuperSpy Mar 18 '12 at 10:37
add comment

You can use @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 track the 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 DATETIME type for the stat_date column, but it's obvious that a DATETIME doesn't make sense for you, since you want only a record for a day not a second. Thus, I substituted the DATETIME type for DATE.

share|improve this answer
I was about to write that MySQL wouldn't let you perform an update with subquery on the same table, but then I noticed you've 'hidden' it with another subquery. Clever trick, and it works! –  Paul Dixon Mar 18 '12 at 10:11
But will that be more efficient? Also note that I am needing a cronjob anyway, since I'll be counting the number of Androids, etc. on the end of the day. This, to not delay my users with extra %like% statements, or strpos() on their userAgents. –  SuperSpy Mar 18 '12 at 10:22
@SuperSpy I have not profiled a table with a DATETIME as PRIMARY KEY versus a table with an AUTO_INCREMENT of type INT. However, logically I believe it would be faster than having to fetch the maximum id and update that record. Then again, this are tiny optimization steps. They fall in the premature optimization boundaries. –  Shef Mar 18 '12 at 10:32
add comment

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

Edit: Too late sorry. :)

share|improve this answer
Thanks anyway ^^, –  SuperSpy Mar 18 '12 at 10:19
add comment

Your Answer


