A simple code to get the next auto increment id from a table in laravel

UserController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;

class UserController extends Controller
{
    /**
     * @return mixed
     */
    private function getNextUserId()
    {
        $statement  = DB::select("SHOW TABLE STATUS LIKE 'users'");
        $nextUserId = $statement[0]->Auto_increment;
      
        return $nextUserId;
    }
}

For me this is always returning same value. I'm using Laravel 10.

So this is my table shipments.

The latest id is 2101. So the next id would be 2102, But it has been returning me 2100;

Everyone says this works in older version of the Laravel but I'm struggling with Laravel 9 and 10.

Do you have any idea?

Hey @anuzpandey
I have no ideia why it's happening in L10

By the way I think you can use this to retrieve the highest id from the table and then increment

$nextId  = DB::table('shipments')->max('id') + 1;

Thank you for your reply @gentildpinto

$nextId  = DB::table('shipments')->max('id') + 1;

This will not be accurate when the latest id gets deleted and this snippet generate the nextId which was already used. (By deleted record).

Thank you for your reply @gentildpinto

$nextId  = DB::table('shipments')->max('id') + 1;

This will not be accurate when the latest id gets deleted and this snippet generate the nextId which was already used. (By deleted record).

Yeah, you're right
I forgot that detail

In MySQL 8 and above, the auto_increment value is cached which is why you're seeing the wrong value when executing your query.

To get the up to date auto_increment value, run SET information_schema_stats_expiry = 0; before the query.

In MySQL 8 and above, the auto_increment value is cached which is why you're seeing the wrong value when executing your query.

To get the up to date auto_increment value, run SET information_schema_stats_expiry = 0; before the query.

Thank's for your hep! :)