In one of the project we were working on, we had a situation, where we need to add a record to the table of the database. The added record id is auto incremented in database. This id needs to added to another table with some more data. Now the issue here is how to get the auto increamented value of the previous table.
In PHP, you can retrieve the auto-incremented ID generated by a INSERT operation in a MySQL database using the mysqli_insert_id() function. Here's how you can do it:
Example Code
Explanation
- Database Connection: Establishes a connection to the MySQL database using
mysqli. - Insert Query: Executes an
INSERT statement. - Retrieve Last ID:
$conn->insert_id retrieves the auto-incremented ID of the last inserted record.
- Error Handling: Checks if the query was successful before attempting to retrieve the ID.
- Close Connection: Always close the database connection after performing operations.
Notes
- Make sure the column is defined as
AUTO_INCREMENT in the table schema. - If you're using a database abstraction library like PDO, you can use
PDO::lastInsertId() instead. - Ensure proper escaping or use prepared statements to prevent SQL injection. For example:
This approach is more secure and should be preferred for user input.
In Laravel, retrieving the auto-incremented ID after inserting a record is straightforward, especially if you are using Eloquent or the query builder.
Using Eloquent
If you are using Eloquent, the save method or the create method automatically returns the auto-incremented ID of the record.
$model = new YourModel();
$model->column1 = 'value1';
$model->column2 = 'value2';
$model->save();
$lastInsertedId = $model->id;
$model = YourModel::create([
'column1' => 'value1',
'column2' => 'value2',
]);
$lastInsertedId = $model->id;
Using Query Builder
If you are using Laravel's query builder, you can use the insertGetId method, which inserts a record and directly returns the auto-incremented ID.
$lastInsertedId = DB::table('your_table')->insertGetId([
'column1' => 'value1',
'column2' => 'value2',
]);
echo "The last inserted ID is: " . $lastInsertedId;
Explanation
Eloquent Approach:$model->save(): Saves the record to the database and updates the $model->id property with the auto-incremented ID.
YourModel::create(): Inserts the record and returns the model instance with the id property set.
- Ensure that the
$fillable property in your model is defined for mass assignment in create().
Query Builder Approach:insertGetId: Inserts the record and directly returns the ID of the inserted record.
Notes- Eloquent automatically manages timestamps (
created_at, updated_at) if the table has them, so you don't need to handle them manually.
- If using
create(), make sure the YourModel class has the $fillable or $guarded property defined:
- For additional security, always validate and sanitize input data before inserting it into the database. Use Laravel's validation mechanisms.