Retrieve the Last Inserted ID

But you should point out that this is dangerous, and in practice should NEVER be needed.

I would think and hope the OP would read the complete articles from the MySql documentation to include:

https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_last-insert-id

Where it clearly states:

Important

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

So naturally like anything you have to learn how to correctly use it.

I would suggest practice on a test database. Always keep a test database with test data while learning new things.

@jlrdw The OP was asking about the last ID of the table. LAST_INSERT_ID() doesn't give you that.

I would suggest practice on a test database. Always keep a test database with test data while learning new things.

That's only going to give them a false sense of security. Fetching the last ID of an auto-incrementing column is a code smell, because the value may grow rapidly due to other users. They're not going to see the resulting bugs when testing the app locally.

Using LAST_INSERT_ID() is especially risky and a big red flag in this context. Let's take this simple piece of code:

$user = User::create($data);
$lastId = DB::select('select last_insert_id()');

Does that give you the user's ID? Well, the user model may have an observer. User creation might trigger an entry into an audit table, which changes the last inserted ID. Or it might trigger a UserCreated event. If that event is queued and the app uses a database queue driver, that changes the last inserted ID again. You get the point.

Don't use last_insert_id() for these kinds of things.

It would be interesting to see the original problem that OP tries to solve. I suspect he doesn't need what he is asking for.

Behind the scenes eloquent uses lastInsertId.

    public function processInsertGetId(Builder $query, $sql, $values, $sequence = null)
    {
        $query->getConnection()->insert($sql, $values);

        $id = $query->getConnection()->getPdo()->lastInsertId($sequence);

        return is_numeric($id) ? (int) $id : $id;
    }

Reference:

https://github.com/laravel/framework/blob/12.x/src/Illuminate/Database/Query/Processors/Processor.php

Which is the same as SELECT LAST_INSERT_ID(); in Mysql.

But again, like anything (rebuilding a jet engine for example), (use Linux dd to clone a drive for example), you have to know what you are doing.

Learn what there is to learn about lastInsertId, then if it fits the use case, use it. If it does not fit the use case, don't use it.

And no it's not dangerous, not bad, none of that. Just like Linux dd you have to know what you are doing.

Don't make things harder than they need to be, remember eloquent is just using regular sql /pdo behind the scenes.

Learn what there is to learn about lastInsertId, then if it fits the use case, use it. If it does not fit the use case, don't use it.

Just like Linux dd you have to know what you are doing.

This should also apply to people giving advice. This is NOT the place to use it. You're suggesting the wrong approach. It doesn't answer the user's question and may cause bugs now or later.

Laravel gets the last ID right after performing a query. The same method isn't guaranteed to work at the app level, and I already listed some common examples where this would break down. Even if it worked at start, later changes may break it in a way that's easy to miss.

If you need the last ID of a table for some reason, query max(id). Don't use last_insert_id() outside the ORM.

You miss the point:

OP said

after saving the new record

The lastInsertId is available already.

The main thing it has to be used prior to more inserts to be useful.

This is not hard to understand. If the OP does do another insert prior to using lastInsertId then of course it is of no use.

So if the one the OP wants is say 50, don't do nothing else prior to storing 50 either in session or a variable. Sorry, but to me it's pretty plain how to correctly use lastInsertId.

You make it sound like a deep mystery, it's not, eloquent uses it behind the scenes to know the child id for child records. I have used it when manually inserting a child record. Never had a problem.

OP said

I suggest you read the rest of what they wrote and what they actually asked.

So if the one the OP wants is say 50, don't do nothing else prior to storing 50 either in session or a variable. Sorry, but to me it's pretty plain how to correctly use lastInsertId.

There's zero reason to do a last_insert_id() query when they already have the ID with $user->id.

I have used it when manually inserting a child record. Never had a problem.

Have you ever worked on an app with model observers, events, or lifecycle hooks?

The reason why last_insert_id() is bad here:

1. They already have the user ID.
2. It also doesn't give you the last ID from users.
3. It may give the wrong ID if any observer, lifecycle hook, etc. triggers other database operations.
4. Even if there are no bugs initially, later addition of an observer may quietly break any piece of code that relies on last_insert_id().

There's zero reason to do a last_insert_id() query when they already have the ID with $user->id.

Please tell me you are joking.

$user->id and PDO::lastInsertId() is the exact same thing.

Which is what I said, the id is already available via PDO::lastInsertId();

I am going by what the framework does here, not eloquent. Eloquent doesn't really exist, it's just shortcuts to, again, regular sql and PDO behind the scenes.

So if not using eloquent or query builder you would use either:

• PDO::lastInsertId()

or

• LAST_INSERT_ID()

Edit:

So if the OP used eloquent their code is correct 100%

$lastInsertedId = $user->id;

But what was actually used is PDO::lastInsertId().

But they did not show all the code used.

Also yes there are cases where it doesn't work, that's why I said the OP needs to understand the function, when to use verses when not to use.

$user->id and PDO::lastInsertId() is the exact same thing.

Firstly, you're conflating two things. PDO::lastInsertId() caches the ID client-side (at least on the MySQL driver), so it doesn't need to invoke a last_insert_id() call.

But more importantly, Laravel gets the last insert ID immediately after running an insert, then sets it on the model. It is guaranteed to be the correct ID from that point on. That may not be the case if you call last_insert_id() later, depending on what the app does, even if the lines follow each other in a controller. That's the whole point. Even if it works at start, it may break later due to changes elsewhere in the code.

I am going by what the framework does here, not eloquent. Eloquent doesn't really exist, it's just shortcuts to, again, regular sql and PDO behind the scenes.

Laravel and PDO also don't exist with this logic. That is such a weird comment. The framework has a lot of features, not just the ones you decide to use.

But I'll stop now. I don't think I'm getting through to you on why it's a bad suggestion.

FIne stop, I am just saying the framework uses exactly what I said (suggested) to use anyway. Which is:

    public function processInsertGetId(Builder $query, $sql, $values, $sequence = null)
    {
        $query->getConnection()->insert($sql, $values);

        $id = $query->getConnection()->getPdo()->lastInsertId($sequence);

        return is_numeric($id) ? (int) $id : $id;
    }

Which, as admitted doesn't work in all cases.

But it is the same as using: SELECT LAST_INSERT_ID(); Referring to Mysql here.

So you are basically saying use $user->id, no don't. Which is it? I need two asprin.

I interpret OP's question:

To get the last inserted id in a table (users) after saving the new record the code is:

As they want the "just inserted" id. Maybe they need to clarify exactly what they want.

But irregardless you basically said not to use something in-built in the framework. If that's the case put in a PR and provide another solution.

chatgpt offers these;

Golden rules for using LAST_INSERT_ID().
✅ Read it immediately
✅ On the same connection
✅ Capture it into a variable
❌ Don’t assume ordering, contiguity, or meaning
❌ Don’t rely on it across triggers or pooled connections

The main point being, the framework inserts and gets the id on adjacent lines on the same connection. Your typical application code might call eloquent to insert a row, and then potentially thousands of executed lines later, they try to get the last insert id.

What is dangerous is that with one user testing, this might work perfectly well, but as the app usage grows or gets more complex, you start to get issues. Best avoided, and probably not what the OP needed anyway.

@snapey good explanation. To add the database used makes a difference also. I use MySql.

And I do have other ways to retrieve an id I need in such cases.