Multiple inserts in a loop codeigniter

Question

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;

Rajeev Radhakrishnan · Accepted Answer · 2017-12-09 09:18:59Z

1

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

answered Dec 9, 2017 at 9:18

Rajeev Radhakrishnan

99410 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

That was my aim. My problem is that how do i restructure my array to this.

John Over a year ago

I think i have no insert it in a foreach loop. since it cannot be inserted as the inert value will be shown as 'array' which will be an error.

Hasta Dhana · Accepted Answer · 2017-12-09 09:41:02Z

0

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';
    }
}

answered Dec 9, 2017 at 9:41

Hasta Dhana

4,7047 gold badges21 silver badges28 bronze badges

3 Comments

John Over a year ago

So in my return statement, i'll just use an if statement and check if $error[] is empty then return true else return $error?

Hasta Dhana Over a year ago

Yes, you could make conditional on the errors like that

John Over a year ago

I accepted your answer because my recent changes came back to this. It seems impossible to just use 1 db query with multiple values(array) as the query will read the data as an array.

Collectives™ on Stack Overflow

Multiple inserts in a loop codeigniter

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related