Increment even if you delete record

Question

Increment even if you delete record

Navigation

#1 by (2 votes)

0

I have the following problem, I have the following function:

public function codigo_denuncia() {         
    $db = new mysql();

    //pegar as linhas do ano
    $ultimoRegisto = $db->executar("SELECT id FROM denuncia WHERE ano=year(now()) ");   

    //verifica se tem registro no ano 
    //se igual ao ano atual, incrementa, se não começa novamente com valor 1
        if($ultimoRegisto->num_rows > 0){
        return $ultimoRegisto->num_rows +1 . '-' . date('Y');
    }

    return 1 . '-' . date('Y');
}

At first it worked OK, always incrementing correctly, the problem occurs if you need to delete a random record from this table, it would generate the next number with duplicate record, for example I have 40 records and I insert one more, the next code would be 41-2017, then delete one record from the table and enter another again, it would generate the same code 41-2017. Any viable alternative to this function? It was done with PHP and mysql and the call is as follows:

$codigo = $this->codigo_denuncia();
$arraycodigo = explode("-", $codigo);
$numero_denuncia = $arraycodigo[0].'/'.$arraycodigo[1];

php mysql

asked by anonymous 22.08.2017 / 14:09

1 answer

Adding the data from a sql column C # Communication between two programs in PYTHON

score 2 · Accepted Answer

You get the number of existing records and adding one more is why it doubles when you remove a record

To fix try to do this, get the id from the last record and add one more:

 public function codigo_denuncia() {         
    $db = new mysql();

    //pegar as linhas do ano
    $ultimoRegisto = $db->executar("SELECT id FROM denuncia WHERE ano=year(now()) ORDER BY id DESC LIMIT 1"); 
    $row = $ultimoRegisto->fetch_assoc();

    //verifica se tem registro no ano 
    //se igual ao ano atual, incrementa, se não começa novamente com valor 1
    if($ultimoRegisto->num_rows > 0){

        return $row['id'] + 1 . '-' . date('Y');
    }

    return 1 . '-' . date('Y');
}