CodeIgniter Function for both Update and Insert

A Generic CodeIgniter Function for both Update and Insert

In this post we will show you A Generic CodeIgniter Function for both Update and Insert, hear for A Generic CodeIgniter Function for both Update and Insert we will give you demo and example for implement.

Here is the generic CodeIgniter model function which you can be used to both update and insert data into the database. The function checks primary key validation automatically so it will update the contents if primary key already exists else it will perform insert query.  The MySQL official documentation for insert of a duplicate key update is given below.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

If you specify, ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in an UNIQUEindex or, PRIMARY KEY MySQL performs the update of the old row. Now our aim is to create a generic model function for CodeIgniter based on above query. The generic function is given below

 public function updateOnDuplicate($table, $data ) {
     if (empty($table) || empty($data)) return false;
     $duplicate_data = array();
     
     foreach($data AS $key => $value) {
        $duplicate_data[] = sprintf("%s='%s'", $key, addslashes($value));
     }

     $sql = sprintf("%s ON DUPLICATE KEY UPDATE %s", $this->db->insert_string($table, $data), implode(',', $duplicate_data));
     
     $this->db->query($sql);
     return $this->db->insert_id();
}

Note

The second parameter of the function must be given as an associated array.

eg $data = array('name' => $name, 'email' => $email, 'url' => $url);

This is a model function and function calling must be done in the controller but since CodeIgniter is a loosely coupled MVC you can use it in controller too.

Function Explanation

The sprinf function is a PHP core function that will return a formatted string. Eg

<?php
$num = 5;
$location = 'tree';

$format = 'There are %d monkeys in the %s';
echo sprintf($format, $num, $location);
?>

The insert_string is a CodeIgniter query builder function which returns a correctly formatted insert query string. Eg

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$str = $this->db->insert_string('table_name', $data);

The first parameter is the table name, the second is an associative array with the data to be inserted. The above example produces:

INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com')

The implode  Join array elements with a string. Eg

<?php

$array = array('lastname', 'email', 'phone');
$comma_separated = implode(",", $array);

echo $comma_separated; // lastname,email,phone

The insert_id is another CodeIgniter query builder function to insert id number when performing database insert.

So we can see that $sql will produce the same syntax as official MySQL UPDATE ON DUPLICATE syntax. Comment below if you have any doubts or suggestions. 

Hope this code and post will helped you for implement A Generic CodeIgniter Function for both Update and Insert. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve onlincode. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs us