循环codeigniter中的多个插入
Let's say in my DB i have 2 tables. One is named Father with Father_id as PK and Father_name as a row. Then i have the second table as Children with Child_id as PK, Father_id as FK, and Child_name as a row.
Children would have a relationship with Father as Many to One. Many Children to One Father.
So let's say the data looks like this in my controller.
Array([Father_id] => 1 [Children] => Array([0] => John [1] => Peter [2] => Michael))
I pass the data above to my model to insert right but i have to restructure it in a way that i can just send it to my database to insert.
foreach($data as $row){
for($temp = 0; $temp < count($data['Child_name']); $temp++){
$query[$temp] = array('Father_id' => $data['Father_id'], 'child_name' => $row[$temp]);
}
}
So now it looks like this,
Array([0]=>Array([Father_id] => 1 [Child_name] => John)[1]=>Array([Father_id] => 1 [Child_name] => Peter)[2]=>Array([Father_id] => 1 [Child_name] => Michael))
I understand how to insert this, i'll just have to do another foreach loop or i can just remove the $temp
in $query[$temp]
and instead put an insert query inside that loop.
My problem is that how can i know that each insert is successful, other than checking the database, i would like to have a return statement like if its only a single insert then if its successful it returns 1.
Also is there any better way to do multiple inserts?
UPDATE:
After some needed break i came back and made some changes.
This is now my model function:
public function insert_children($data){
foreach($data['child_name'] as $row){
$query = array('father_id' => $data['father_id'], 'child_name' => $row);
$result = $this->db->insert('children',$query);
}
//return something here
}
It is essential to return something back to the controller right? for example, i want to return if the inserts were successful or not. So is there any already made function in codeigniter for this? or do i have to make myself.
for example, i can just make a $temp
variable to contain an incremental value each time an insert is successful and compare it at the end of the loop to the count($data['children'])
So if there are 3 children the value of $temp should be 3 or 2 depending on declaration if i specify it with 1 like $temp = 1;
You can use affected_rows()
function on each loop item to check whether data is inserted or not and return error message only for the data that is not inserted :
foreach($data as $row) {
for($temp = 0; $temp < count($data['Child_name']); $temp++){
// note that I remove [$temp] below
$query = array('Father_id' => $data['Father_id'], 'child_name' => $row[$temp]);
$this->db->query($query);
if($this->db->affected_rows() == 0) // data not inserted, return error message
{
$error[] = $row[$temp];
}
}
}
// shows error if one or more child name is not inserted
if (isset($error)) {
foreach ($error as $child_name) {
echo $child_name . ' is not inserted';
}
}
A General way to insert multiple data
$data = array(
array('father_id'=> 'id', 'child_name' => 'Unni' ),
array('father_id'=> 'id', 'child_name' => 'Kuttan' ),
//...
);
$this->db->insert('mytable', $data);