In a recent project I have to show and use the next user_id as username ( I know it is a bad decision, but this is how the client wants it).
I would usually use
but that works only if you already inserted a row before.
So I had to use MySQL SHOW TABLE STATUS :
// Get the latest ID from the users table and pad it with zeroes
$sqlstat = "SHOW TABLE STATUS WHERE name='wp_users'";
$user_login = str_pad($wpdb->get_row($sqlstat)->Auto_increment , 5, 0, STR_PAD_LEFT);
That is doing query to the wp_users table status, and I am getting back the value of the NEXT user_id (to be displayed later in the “Create a new user form”).