php – Get first idle or free id in mysql

Question:

I would like to know if there is any way to know the first free mysql record, but not the last one, if not the first free one that exists.

Ex:

I have busy registration 1,2,3,6,7,8,9 the first free would be 5

At the moment I have a code that tells me the last one that would be this:

$CIdmaxFact = "SELECT MAX(CAST(codfactura AS UNSIGNED)) AS ultimocod FROM Facturas WHERE empresa='".$_SESSION['Empresa_Id']."' AND serie='".$_POST["Infactura_serie"]."' "; //miramos de nuevo el ultimo codigo por si aca
        $RIdmaxFact = mysqli_query($Conectar ,$CIdmaxFact); //pongo un resultado de la consulta
        $DIdmaxFact=mysqli_fetch_array($RIdmaxFact); $idmaxFact = trim($DIdmaxFact["ultimocod"]); $idmaxFact ++;
        $idmaxFact = str_pad($idmaxFact, 4, "0", STR_PAD_LEFT);

But it does not serve me at all, since it only tells me the last one. Any ideas?

Answer:

You can use this little "trick"

SELECT t.id +1
FROM Table1 t
WHERE NOT EXISTS (
    SELECT * 
    FROM Table1 t2
    WHERE t2.id = t.id + 1
)
LIMIT 1

I hope it helps you.

Scroll to Top