Bulk insert update and delete data in codeigniter

Bulk insert update and delete data in Codeigniter.

Why do you need to use the bulk insert, update, and delete in Codeigniter? There are many reasons; one reason is that you are fetching the id of a table and inserting that id in a new table.

If you call insert query in the loop, yes, you can do that, but it can decrease your system performance, so how can you insert the values..? Codeigniter is proving the bulk-adding functionality using query builder; you need to call the method in your model $this->db->insert_batch().

Imagine if you have millions of records inside your loop, which means you call the insert query millions of times; this is not a good practice if you work on the MVC Framework.

foreach ($afterTripQuery as $sendAfter) { 
    $this->db->insert('tableName',array('columnName',$sendAfter['columnName'])) 
}

Here is the simple solution to your problem; as you can see below, you are inserting many records using a single query, depending on how you code your project.

$arrayBatch =  array(); 
foreach ($afterTripQuery as $sendAfter) {
     $arrayBatch[] = array('columnName'=>$sendAfter['columnName']);  
} 
$this->db->insert_batch('tableName', $arrayBatch);

There are many ways to use the Batch insert and update it. CodeIgniter for supposing you are creating the blog system where you have a relational database like the blogs table and the tags table, in the tags table, you are sending the blog's id as a foreign key.

You can send the tags' data in the array from HTML and now break the operation to insert your blog.

Step 1:  Insert the blog content in the blogs table, get the table's currently added id, and send it to the tags table to enter the tag elements below the example code.

Here is the model's method. We are inserting the blog content and fetching the currently added id.

public function addblog($value) { 
    $this->db->insert('blogs',$value); 
    return $this->db->insert_id(); 
}

Now we can fetch the tag's data, which is coming from the HTML

$data['b_slug'] = $this->input->post('blog_slug',true);//fetching the data from the  html 
$blogtags = array(); 
foreach ($tags as $tag) {    
    $blogtags[] = array(
    'blog_id'=>$blogId,//this is current blog id       
    'tag_name'=>$tag,       
    'tag_created'=> date("Y-m-d h:i:sa")    
    ); 
} 
$this->db->insert_batch('tags', $arrayBatch);

If you want to update the bulk records in CodeIgniter it's very simple just use the update_batch from query builder  $this->db->update_batch('mytable', $data, 'columnName');

$blogtags = array(); 
foreach ($tags as $tag) {    
    $blogtags[] = array(       
    'blog_id'=>$blogId,//this is current blog id       
    'tag_name'=>$tag,    
    ); 
} 
$this->db->update_batch('tags', $arrayBatch,'blog_id');//pass the columnName in the third parameter

So now ho can you delete your multiple records with a single query It's very simple just use the where_in method in query builder  $this->db->where_in('columName',array())

$blogtags = array(); 
foreach ($tags as $tag) {    
    $blogtags[] = $tag['tagId'];    
); 
} 
public function deleteTags($blogtags) {
  $this->db->where_in('tagId', $data);  
  $this->db->delete('tags'); 
}

https://www.youtube.com/watch?v=8T0dkmv1ZQI