Codeigniter- How to insert different sizes of arrays into database

Question

I want to insert data in table 'projects'. I have two arrays with different sizes, they are

$advisor_id = array(
         'id' =>1
         'id' =>2
         'id' =>3
      );


$project_id = array(
      'pid'=>1
      'pid'=>2
      'pid'=>3
      'pid'=>4
   );

My code is:

$advisors = count($this->input->post('advisor_id[]'));   
$PM_ids = count($this->input->post('PM_id[]'));
if($advisors > $PM_ids){
    $count = $advisors;
}else{
    $count = $PM_ids;
}

$data[] = array();
for($i =0; $i<$count ; $i++){
    $data = array(                      
          'advisor_id' =>$this->input->post('advisor_id')[$i],
          'PM_id' =>$this->input->post('PM_id')[$i],
    );     
    //print_r($data);
    $this->db->insert_batch('project_config',$data);     
}

My problem is the different sizes of arrays. How can I insert into database.

In the above example data, what is your desired output (or table after insert)? — kchason, Nov 22 '17 at 12:34
i need to insert two arrays with different sizes to database table — Narsimhulu, Nov 22 '17 at 12:38
I see that from your title, but that doesn't help us understand what the actual goal is. From what I see above, you want 4 entries, but I'm not sure what you want the 4th value of `advisor_id` to be. — kchason, Nov 22 '17 at 12:39
Both arrays are illegal format. They can't contain an index more than one time — splash58, Nov 22 '17 at 12:54

mickmackusa · Accepted Answer · 2017-11-23T06:07:04.093

If you are happy to submit NULL values where missing, array_map() is the best tool for the job.

First a general demonstration of how/where NULL elements are generated when array size mismatches occur...

Code: (Demo)

$advisor_id=['id1'=>1,'id2'=>2,'id3'=>3];
$project_id=['pid1'=>1,'pid2'=>2,'pid3'=>3,'pid4'=>4];
$data=array_map(function($id,$pid){return ['advisor_id'=>$id,'PM_id'=>$pid];},$advisor_id,$project_id);
var_export($data);  // see how NULL elements are formed in the output array

Output:

array (
  0 => 
  array (
    'advisor_id' => 1,
    'PM_id' => 1,
  ),
  1 => 
  array (
    'advisor_id' => 2,
    'PM_id' => 2,
  ),
  2 => 
  array (
    'advisor_id' => 3,
    'PM_id' => 3,
  ),
  3 => 
  array (
    'advisor_id' => NULL,
    'PM_id' => 4,
  ),
)

The above multi-dimensional array is now fully prepared for a single insert_batch() call. IMPORTANT CodeIgniter's insert_batch() is specifically designed to INSERT multiple rows of data at once -- to use this call inside of a loop defeats the purpose of making this call.

As for you actual implementation, this should work (I don't code in CodeIgniter):

$data=array_map(function($id,$pid){return ['advisor_id'=>$id,'PM_id'=>$pid];},$this->input->post('advisor_id'),$this->input->post('PM_id'));
$this->db->insert_batch('project_config',$data);

CLEAN & DONE: No for loops, no $i counters, and just one call to the database.

Thanks for improving the code and the nice documentation. Much appreciated :-) — Philipp Maurer, Nov 27 '17 at 13:41

Codeigniter- How to insert different sizes of arrays into database

1 Answers1