PHP使用来自数据库的最新版本

Question

I have a value in my database called revision which increases when there's a duplicate entry.

I'm trying to select the latest revision in my database. Example:

|-----------------------------|
| Title            | Revision |
|-----------------------------|
| Some title 1     | 0        |
| Some title 1     | 1        |
| Some title 1     | 2        | <-- select this one
|-----------------------------|

This is my query (just for example, I know the last argument will fail):

$titlecheck = $this->query("SELECT * FROM titles WHERE tmdb_name=:name AND tmdb_titleid=:skuid AND revision=:revision");
if($titlecheck->execute(array(":name" => $api[1]['tmdb']['name'], ":skuid" => $api[1]['tmdb']['titleid'], ':revision'=>LARGEST))) {
    // do something
}

How can I select the latest revision number? I have looked into MAX() but since I'm required to select all from the database I won't be able to use this.

Answer 1

You need to use ORDER to ensure certain order of returned rows. Otherwise you may get it in random order:

SELECT * FROM titles 
WHERE tmdb_name=:name AND tmdb_titleid=:skuid
ORDER BY revision DESC

then the first row will be the one with highest Revision. If you need just that, use LIMIT 1 at the end of query and ensure revision column has index.