phpcodeigniteractiverecordinsertselect-insert

codeigniter insert from query how to optimized


I want to optimized my query on codeigniter.

this query works but it seems, its take time to insert the result to my database table.

$this->db->select('client_id');
$this->db->from('event');       
$query = $this->db->get();  

foreach($query->result_array() as $row){
  $client_id = $row['client_id'];
  $data = array( 'event_id' => $event_id , 'client_id' => $client_id);          
  $this->db->insert('event_entry', $data);                  
}

I would like to know if theres a way it to optimized.


Solution

  • Instead of doing n number of inserts, doing just a single insert should improve execution time. You can achieve this in codeigniters active record by using insert_batch() .

    $this->db->select('client_id');
    $this->db->from('event');       
    $query = $this->db->get();  
    
    $data = array();
    
    foreach($query->result_array() as $row){
      $client_id = $row['client_id'];
      array_push($data, array('event_id' => $event_id , 'client_id' => $client_id));          
    }
    
    $this->db->insert_batch('event_entry', $data); 
    

    Produces:

    INSERT INTO event_entry (event_id, client_id) VALUES ('event_id', 'client_id'), ('event_id', 'client_id'), ...