Laravel Query Builder: Insert and Get ID of Last Inserted Row

Introduction
Setting the Stage
Conclusion

Introduction

Laravel, a popular PHP framework, facilitates database operations with Eloquent ORM and Query Builder, streamlining the database handling process remarkably. This guide offers a comprehensive look at using Laravel’s Query Builder to insert records into a database and retrieve the ID of the last inserted row. We’ll cover basic to advanced concepts, complemented by code examples to solidify your understanding.

Setting the Stage

Before diving into examples, ensure that you have Laravel installed and a database configured. After setting up your database credentials in Laravel’s .env file and creating a database migration, you’re ready to begin.

Basic Insert Operation

The simplest form of insertion with Query Builder is as follows:

DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => Hash::make('password')
]);

This snippet inserts a new user into the users table. However, it does not return the ID of the created record.

Inserting and Retrieving the Last Insert ID

To get the ID of the newly inserted row, you can use the insertGetId method:

$id = DB::table('users')->insertGetId([
    'name' => 'Jane Doe',
    'email' => '[email protected]',
    'password' => Hash::make('password')
]);

// Output: the ID of the newly inserted row
echo $id;

The insertGetId method returns the auto-incrementing ID of the inserted record, which is particularly useful when you need to perform additional operations using this ID immediately after the insertion.

Conditional Insertions and ID Retrieval

In more complex scenarios, such as conditionally inserting records, you may wish to perform checks before inserting:

$email = '[email protected]';

$exists = DB::table('users')->where('email', $email)->first();

if (!$exists) {
    $id = DB::table('users')->insertGetId([
        'name' => 'John Doe',
        'email' => $email,
        'password' => Hash::make('password')
    ]);
    echo "Inserted user with ID: $id";
} else {
    echo "User already exists.";
}

This script first checks if a user with the given email address exists. If they don’t, it inserts the new user and returns the ID.

Advanced Usage: Bulk Insertions and ID Retrieval

While Laravel’s Query Builder doesn’t natively support retrieving IDs for bulk insertions, you can employ a transaction to manage a manual iteration:

$users = [
    ['name' => 'Alice', 'email' => '[email protected]', 'password' => Hash::make('password')],
    ['name' => 'Bob', 'email' => '[email protected]', 'password' => Hash::make('password')]
];

$insertedIds = [];

DB::transaction(function () use ($users, &$insertedIds) {
    foreach ($users as $user) {
        $insertedIds[] = DB::table('users')->insertGetId($user);
    }
});

// Output: Array of the IDs of the newly inserted rows
print_r($insertedIds);

The code above uses a database transaction to ensure that if any insertion fails, none of them are committed to the database, preserving data consistency.

Conclusion

This guide has illustrated various techniques to insert data and retrieve the ID of the last inserted row using Laravel’s Query Builder. By understanding these methods, you can enhance your database interactions within your Laravel applications.

Next Article: How to update JSON column in Laravel Query Builder

Previous Article: Bulk Insert/Update in Laravel Query Builder: Tutorial & Examples

Series: Laravel & Eloquent Tutorials

PHP

PHP