Show last id of database in PDO

Question

I am trying to get the last id number of Database. But It's showing only 0. I am learning PDO. Can anyone tell me how can I do it?

if($_SERVER['REQUEST_METHOD']=='POST'){
  $sql = "SELECT * FROM tablename";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  $row = $stmt ->fetch();
  $showid = $pdo->lastInsertId();
  echo $showid;
}

You don't insert anything here, that's why insert id is not available. — u_mulder, Sep 28 '17 at 08:46
`lastInsertId()` to quote the manual, *Returns the ID of the last inserted row*; your query was a SELECT query, not INSERT. You probably want something like `SELECT table.id FROM table ORDER BY table.id DESC LIMIT 1` — CD001, Sep 28 '17 at 08:46
`lastInsertId` doesn't works like that. Check its documentation: http://php.net/manual/en/pdo.lastinsertid.php . If your ids are auto incremented, then the last id is the bigger. — Federkun, Sep 28 '17 at 08:46

score 6 · Answer 1 · edited Sep 28 '17 at 14:06

6

lastInsertId will return the ID of a row that was inserted by the same connection. You can't use it outside of that context.

The easiest way to fetch the last ID would be to run something like,

SELECT max(id) FROM tablename

If you're using this in order to work out which ID should be inserted next, consider using an auto-increment column for your ID instead.

edited Sep 28 '17 at 14:06

Script47

14,230
4
45
66

answered Sep 28 '17 at 08:46

iainn

16,826
9
33
40

score 0 · Answer 2 · edited Sep 28 '17 at 10:47

0

lastInsertId() works only after a INSERT query - since you're only doing a select, it will always return string(1) = 0

So either you perform this code after an INSERT statement, or you can do the following:

$stmt = $db->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt->fetchColumn();

Refering here to: PDO get the last ID inserted

Visit this thread, you'll find my samplecode provided by Corbin and much more informations on this topic.

edited Sep 28 '17 at 10:47

Script47

14,230
4
45
66

answered Sep 28 '17 at 08:47

Twinfriends

1,972
1
14
34

I tried your link and then It shows only 0 but this is supposed to show 2. Any Clue ? – Ami Hasan Sep 28 '17 at 09:06

Show last id of database in PDO

2 Answers2