PHP SQL assign LAST_INSERT_ID() into a variable

Question

I am trying to assign LAST_INSERT_ID() into a variable. When I use LAST_INSERT_ID() it works but when I do $id = LAST_INSERT_ID(); it doesn't work.

Works:

INSERT INTO pooruser (userid, name) values (LAST_INSERT_ID(), 'Jack')

Doesn't Work:

$id = LAST_INSERT_ID(); 
INSERT INTO freeuser (userid, name) values ('$id', 'Jack')

I'm not really sure why it won't work. If anyone can help me I would greatly appreciate it!

Also if I can get this to work to store that in a session would I then do $_SESSION('id') = $id; ?

Again, any help is appreciated!

Answer 1

You are using LAST_INSERT_ID() wrong:

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

If you want to do it with SQL instead of the PDO API, you would do it like a normal select query:

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

Resource: PDO get the last ID inserted

Here examples for other connections: https://www.w3schools.com/php/php_mysql_insert_lastid.asp

For session:

$_SESSION['id'] = $id;