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?