Home / Questions / 218246
Accepted
Javier fr
Asked: 2020-11-30 12:59:42 +0800 CST 2020-11-30 12:59:42 +0800 CST

Insert into two tables at once

  • 772

I have the general table

enter image description here

I also have the line table

enter image description here

And finally I have the table company_line

enter image description here

What I want to do is that when I insert in the general table, the id_general of the new record is passed to the company_line table and also put in the same table and the same record the id_line that I already get from the form

My Controller with the data I want to insert as an example

public function add_empresa_linea(){//aqui debo de insertar en dos tablas
  $data = array(
    'nombre' => $this->input->post('txt_nombre'),
    'id_servicio' => 1,  
  );

  $id_linea = $this->input->post('combo_linea');

  $insert = $this->reg_calls_model->add_empresa_linea($data);

  if ($insert) {
    echo json_encode(array("status" => TRUE));  
  }
}

My model

function add_empresa_linea($data){
  try {   
    $result = $this->db->insert('general',$data);

    return $result;
  } catch(Exception $e) {
    show_error($e->getMessage() . ' --- ' . $e->getTraceAsString());
  }
}
mysql

1 Answers

  1. Best Answer

    What you can do is open a transaction with BEGIN , do all the INSERT , UPDATE , DELETE , even the SELECT that you need, and once you consider that everything is fine, do COMMIT and that's it, this way you have what you need. You'll have to find the equivalent of this in your framework, while I give you an example in SQL:

    BEGIN;
INSERT INTO general (nombre, ...) VALUES ("prueba", ...);
INSERT INTO linea (numero, ...) VALUES ("55512345", ...);
SELECT id INTO @id_general FROM general WHERE nombre = "prueba";
SELECT id INTO @id_linea FROM linea WHERE numero = "55512345";
INSERT INTO empresa_linea (id_general, id_linea) VALUES (@id_general, @id_linea);
COMMIT;

    I clarify that the three points (...) is to indicate the rest of the columns & values ​​that must be completed, this is an example.

    • 2