0

Is there a method to get last select ID in a similar way to lastInsertId? For example:

<?php
$stmt = $db->prepare('SELECT * FROM users WHERE user_id = :user_id');
$stmt->bindValue(':user_id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_OBJ);
$user_id = $db->lastSelectId('user_id'); // what can I do here?
?>

Obviously in the above example I could simply get the last selected row ID with $user->user_id but that's not the question. Any ideas?

TimD
  • 462
  • 6
  • 18
  • If you do a `SELECT *` you get every field from the database. So, if there is an ID field inside `users` you will also obtain the ID from the query. – Juarrow May 30 '15 at 10:50

5 Answers5

1

If you want to select the last inserted row from your database table, there is no point selecting all rows and then looking for the last in a loop. Besides, user_id should be primary key, in which case you query should only return one row.

If user_id is an auto-incremented field, your query should go like SELECT * FROM users ORDER BY user_id DESC LIMIT 1, this will return the user with the largest user_id.

I will also suggest the you save the timestamp of when users are inserted and then you can do ORDER BY date_added DESC LIMIT 1 this will work irrespective of the ORDER of the user_ids.

IROEGBU
  • 948
  • 16
  • 33
  • last row ain't always the last inserted. wrong logic – Dice Aug 24 '18 at 22:10
  • @Dice if he's not doing something funny, sorting by date inserted will always give last inserted record (AI PK too - though I wouldn't use that). – IROEGBU Aug 28 '18 at 14:03
  • My bad, I either didn't read well your statement, either you edited to add the timestamp trick. Your query is the best possible one for this usage. However if he does some insert he can use lastInsertId() and just store that in the session. Easiest way to retrieve an ID to me instead of making the query everytime – Dice Aug 28 '18 at 15:34
  • @Dice yes, lastInsertId() is the best option. But, I assumed he didn't want to use that (looking at his first sentence), for whatever reason... – IROEGBU Aug 30 '18 at 10:10
0

No, there is no other way than reading $user->user_id. No magic functions to get the last id of a select.

And it's probably because there is no need for it, since the select returns that value itself. You've shown in your question how easy it is to read the id.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
0

Try With following - $stmt->insert_id;

Refer the below link - Using PHP, MySQLi and Prepared Statement, how I return the id of the inserted row?

Community
  • 1
  • 1
Anurag Ghube
  • 1
0

Make sure you use LIMIT 1 if you're searching for one specific user.

$stmt = $db->prepare('SELECT * FROM users WHERE user_id = :user_id LIMIT 1');
$stmt->bindValue(':user_id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_OBJ);
echo $user->user_id;
rybo111
  • 12,240
  • 4
  • 61
  • 70
0

Here are my solutions :

  • Put the desired ID of the last SELECT in the $_SESSION['name'].
  • Put it in the html file as a Hidden Input.
  • Go fetch it at the beginning of you controller file so it's always set and ready to use.
  • Use lastInsertId();
Dice
  • 236
  • 1
  • 8