How to save the ID of an insertion in BD in another table in PHP?

0

I have three tables:

MAIN : ID_main, date, name

REGISTER : ID_register, data

MODIFICATION : ID_modification, data, register_ID

MASTER : ID_master, main_ID, register_ID, modification_ID

Operation:

  • I fill in the MAIN data.
  • Then I fill the REGISTER (I can generate as many as I want)
  • Followed if the REGISTERs meet X value, for each trained REGISTER another FORM is generated that adds the MODIFICATION (it can be NULL)
  • Finally I keep the ID of the main in the MASTER, because this will be repeated for each REGISTER with each MODIFICATION

AN EXAMPLE:

Register
ID_Register   VALUES
1             1000, 1500, 16000
2             1500, 6500, 95000
3             1000, 9000, 98000

Modification
ID_modification VALUES register_id
1               +500   1
2               -500   2
*/* NO SE MODIFICA EL TERCER REGISTER */*

Main
ID_main   date       name
1         25/05/2017 carros

Master
ID_master  id_main id_register  id_modification
1          1       1            1
1          1       2            2
1          1       3            NULL

I have the following structure to insert the REGISTER IDs in the MODIFICATION.

NOTE: since I get all the data in an array called $ allData, I check the name of the key to know if it was a modification or a record, then I check if the key of the modification is within the values of the modification array.

After that I do the conversions to JSON and insert the data, the problem I get when preparing the second sentence.

    $query_a = "INSERT INTO register (datas) VALUES (:datas)";

    $stmt= new Connection();
    $stmt->preparar($query_a);
    $allData = array( 

        ["main"] => array(["date"] => 25-01-2017, ["name"] => "example")
        ["values1"] => array(1000, 1000, 1000),
        ["values2"] => array(2000, 1000, 2000),
        ["modification1"] => array(500, -500, 0),
        ["modification2"] => array(50, 10, 10 ),
    );
    $modificationType = ["modification1", "modification2", "modification3", "modification4", "modification5"]; 

    foreach($allData as $key => $value) {
    // insertar register
        if($key !== "main" and !in_array($key, $modificationType)) {
            ${"$key"."_JSON"} = json_encode($value);
            $stmt->asignar("datas", ${"$key"."_JSON"});
            $stmt->ejecutar();
            $id = $stmt->ultimoRegistro();
        }
    }


    $query_b = "INSERT INTO modification (data, register_id) VALUES (:data, :register_id)";
    $stmt->preparar($query_b);
    //insertar modification
    foreach($allData as $key => $value) {
        if($key !== "main" and in_array($key, $modificationType)){
                ${"$key"."_JSON"} = json_encode($value);
                $stmt->asignar("data",${"$key"."_JSON"});
                $stmt->asignar("register_id", $register_id);
                $stmt->ejecutar();
        } 
    }

So far I do not have the insertion of the MASTER or the MAIN, but I would like to insert in the MODIFICATION the ID of each record in the REGISTER table.

How can I manage to insert the ID of each REGISTER within each insertion of MODIFICATION?

    
asked by Victor Alvarado 19.02.2018 в 21:13
source

0 answers