CODEWITHSUNDEEP
phpmysql
SuperSpy
SuperSpy

Reputation: 1314

Can you update a row with the highest ID in one query?

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.

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

Table stats => id | views

Upvotes: 3

Views: 183

Answers (3)

Shef
Shef

Reputation: 45589

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.

Upvotes: 4

justanotherhobbyist
justanotherhobbyist

Reputation: 2190

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

Edit: Too late sorry. :)

Upvotes: 2

dev-null-dweller
dev-null-dweller

Reputation: 29482

Yes:

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

Upvotes: 7

Related Questions