Use the id for an insert without auto increment

Question

Use the id for an insert without auto increment

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

In MYSQL I have a table of subscribers where the primary key is the subscription number which is a column that does not have auto-increment, I can not change that. I made, in Codeigniter, a method that takes the next id for insertion like this:

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

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

The question is whether there is a way if you do this during insertion using the same codeigniter, php or sql. If it does not exist, by doing so I quote do I run the risk of two concurrent entries taking the same registration number?

php mysql codeigniter

asked by anonymous 09.03.2016 / 18:30

2 answers

What's wrong with my code? It gets giving error "[Error] ld returned 1 exit status" Problem using Bootstrap Tabs

score 1 · Answer 1

I believe that an interesting solution in this case would be to create a Trigger in the database. Thus, you could define that before entering a new record, get 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 before inserting into the inscribed table (BEFORE INSERT ON subscribers), for each line inserted (in case it will only have one) the field registration of the new record (NEW.inscription) will be the max of inscription + 1.

score 0 · Answer 2

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

This method returns exactly the last ID inserted because it uses the last_insert_id() function of the MySQL , so that it does not have the error of concurrent inscriptions taking 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.