How to generate code before entering data into the database

Question

I need to generate a code that matches id which will be auto-incremented in the database. It will be a routine that checks the last id generated and generates a code that will be the id later before data entry.

For example:

If the last id was the id 2, routine will generate code 3 which is the id which will be auto-incremented when entering the data into the database.

How can I get this result with PHP and Mysql?

Answer 1

Follows the query to get the next ID:

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'nome_da_tabela'
AND table_schema = DATABASE();

Or one of these:

SHOW TABLE STATUS FROM nome_da_base_de_dados LIKE 'nome_da_tabela';
SHOW TABLE STATUS FROM nome_da_base_de_dados WHERE Name='nome_da_tabela';

Note that these darlings are very specific, probably only serving in Mysql. The option with SHOW TABLE STATUS returns multiple columns, between them the next auto-increment.

An alternative would be you give an UPDATE on the line you just inserted, using the column id + 1, which gives in the same to obtain the last_insert_id().

Anyway, it is worth saying that probably this solution that you are looking for sooner or later will give problem, either by failure in insertion, two users trying to insert almost at the same time, or even the simple data mismatch at the time of some removal.

I can’t say this, because you didn’t give details of what you’re doing, but by the initial question, I have a slight feeling that whatever you’re trying to do, there may be other ways to resolve.

Answer 2

Be able to solve the problem with the following code:

LESS CREATIVE OPTION

public function geraCod(){
        //Conexão
        $db = new MysqliDb('localhost', 'root', 'usbw', 'atendimento');
        //Seleciona o último código
        $new_cod = $db->query('SELECT MAX(cli_cod) as id FROM clientes');
        $new_cod = $new_cod[0];     

        $new_cod =  ($new_cod['id'] + 1);
        echo str_pad($new_cod, 4, "0", STR_PAD_LEFT);
    }

SECOND OPTION:

public function geraCod(){
        $db = new MysqliDb('localhost', 'root', 'usbw', 'atendimento');
        $cols = Array ("clientes");
        $new_cod = $db->rawQuery("SHOW TABLE STATUS FROM atendimento WHERE Name =  ?", $cols);
        $new_cod = $new_cod[0];     

        echo $new_cod['Auto_increment'];
    }