Insert in two tables at once


I have the general table

I also have the table line

And finally I have the company_line table

What I want to do is that when I insert in the general table the general_id of the new record is passed to the company_line table and also put in the same table and the same record the line_id 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());
asked by Javier fr 29.11.2018 в 21:59

1 answer


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

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);

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

answered by 30.11.2018 / 00:28