Use id for auto-no-increment insertion

Asked

Viewed 918 times

0

In the MYSQL I have a table of subscribers where the primary key is the registration number which is a column that has no auto-increment, I can’t change that. I made, in Codeigniter, a method that picks up the next id for insertion like this:

SELECT MAX(incricao)+1 AS inscricao FROM inscritos;

This is done in the same insertion, I already have the object ready, so I put this value in the object and insert it into the database and it works.

The question is whether there is a way to do this while inserting using the same codeigniter, php or sql. If there is not, by doing what I quoted I run the risk of two simultaneous registrations taking the same number of inscription?

php mysql codeigniter
  • 1

    If anyway you are manually incrementing numerically in 1, I do not see why not use auto-increment of the bank. And yes, the way you are doing you will get conflict problem of Keys yes. What is the reason of not using the bank?

  • The database is from a school, this table is used by other applications, so I was not allowed to change it.

  • Since you cannot change the existing table, then create a table only for auto_increment and get the ID like this: SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'nome_table_incrementadora'
AND table_schema = DATABASE( ) ;

  • Or SHOW TABLE STATUS LIKE 'nome_table_incrementadora'

2 answers

1

I believe that an interesting solution in this case would be to create a Trigger in the database. Thus, it would be possible to define that before entering a new record, take the next available value and use it as ID.

For example:

CREATE TRIGGER TriggerIncrementInscritos BEFORE INSERT ON inscritos
FOR EACH ROW BEGIN
    SET NEW.incricao = SELECT MAX(incricao)+1 AS inscricao FROM inscritos;
END

That is, create a trigger (create Trigger) before inserting into the entered table (BEFORE INSERT ON subscribers), for each inserted row (in case there will only be one) the entry field of the new record (NEW.entry) will be the + 1 entry max.

0

In Codeigniter there is the method $this->db->insert() that returns the ID of the last record inserted in the database.

This method returns exactly the last ID inserted as it uses the function last_insert_id() of MySQL, thus making sure you don’t have the error of simultaneous registrations by getting the same id.

$data = array(
    'nm_contato' => $this->input->post('txt_nm_contato'),
    'nm_razao_social' => $this->input->post('txt_nm_razao_social'),
    'nm_fantasia' => $this->input->post('txt_nm_fantasia'),
    'nm_holding' => $this->input->post('txt_nm_holding'),
    'ds_email_contato' => $this->input->post('txt_ds_email_contato'),
    'cd_telefone' => $this->input->post('txt_cd_telefone'),
    'ds_inscricao_estadual' => $this->input->post('txt_ds_inscricao_estadual'),
    'ds_inscricao_municipal' => $this->input->post('txt_cli_cliente_ds_inscricao_municipal'),
);

$this->db->insert('tb_cliente', $data);
echo $this->db->insert_id();

Note: This method can be used in both Codeigniter 2 and 3.

Browser other questions tagged php mysql codeigniter

You are not signed in. Login or sign up in order to post.