CodeIgniter- active record insert if new or update on duplicate

Question

Is it possible to do an active record query in CodeIgniter that will update an existing record if one already exists or insert if it doesnt, for the given key?

I understand this could be done by first querying to find an existing record, but I'm seeking the most efficient approach.

Rosario

Answer 1

Basically what you are looking for might be this INSERT ... ON DUPLICATE KEY UPDATE - provided that you are using MySQL and your id is a unique key on the table.

You'd have to manually construct the query and pass to the $this->db->query() function instead of any built in active record like helper functions of the DB Driver.

Example:

$sql = 'INSERT INTO menu_sub (id, name, desc, misc)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            name=VALUES(name), 
            desc=VALUES(desc), 
            misc=VALUES(misc)';

$query = $this->db->query($sql, array( $id, 
                                       $this->validation->name, 
                                       $this->validation->desc, 
                                       $this->validation->misc
                                      ));

Alanna

Is this active record though? – Spew

Obviously not, if you are constructing your own query. – Astrea

Answer 2

If you use Codeigniter 3.0 or higher, you might want to consider using "$this->db->replace()". According to the user guide:

"This method executes a REPLACE statement, which is basically the SQL standard for (optional) DELETE + INSERT, using PRIMARY and UNIQUE keys as the determining factor. In our case, it will save you from the need to implement complex logics with different combinations of select(), update(), delete() and insert() calls."

In other words,

• If it doesn't exist, it inserts a new record.
• If it does exist, it updates it according to its primary or unique key.

Just use it straight out of the box like:

$data = array(
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
);

$this->db->replace('table', $data);

No batteries required!!!

Gillyflower

but this have a problem with update,when have auto increment id, it increase when replce – Dominations

@SameeraPrasadJayasinghe "... REPLACE INTO essentially deletes the row if it exists, and inserts the new row ..." - from a comment by Dale on the answer here: https://mcmap.net/q/45575/-insert-into-a-mysql-table-or-update-if-exists – Jodiejodo

Answer 3

You could do it simpler:

$sql = $this->db->insert_string(table, $array) . ' ON DUPLICATE KEY UPDATE ' .
implode(', ', $array);
$this->db->query($sql);

Before

This is incomplete and will not work as posted. implode will only combine the array values, without quotes and without the field names. And if you passed primary key as one of them it will be included, which it should not be. – Astrea

Answer 4

I doesn't know Codeigniter Active Record Class has this method or not check the codeigniter docs for the methods containing in active record class

But you can achive this throug extending core models of codigniter. By using this way you can use this method for all the models which extends this model class. Just place the MY_model.php into application/core/ and write the following code.

Class MY_Model extends CI_Model
{
  public function insert_update($data)
  {
       // code for checking existing record.
       if(//existing record)
           fire the update query
       else
           fire the insert query

       return insert/update id;

  }
}

after creating the above file You have to change the All your models parent class to the new Extended model i.e. MY_Model

class some_model extends MY_Model

NOTE: You have to select the primary key from results and put it into the where condition.

It's very critical so what I do when I get the data from the controller I just check it have the ID or not if Id is present then I fired the update query if not then I fired The Insert Query.

BEST OF LUCK

Chelseachelsey

OP already knows this method. He wants to know any available efficient way. – Alanna

Answer 5

I'm using this approach:

1. configure your table mytable with unique id and unique key for the column xyz, which you want to update

2. try to insert an array $data, using INSERT IGNORE INTO do avoid duplicates

   $insert_query = $this->db->insert_string('bt_ical_list', $data);
   $insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
   $this->db->query($insert_query); 
   

   this inserts a row, if value in column xyz doesn't exist

3. use function insert_id() to return an id if row was inserted, update if no row was inserted.

   if(!$this->db->insert_id()){
       $query=$this->db    ->where('xyz', $data['xyz'])
                           ->update('my_table',$data);
   };  
   

Hamill

Answer 6

Here is a method that I hope can accomplish the same

   /**
    * Used to insert new row if a duplicate entry is encounter it performs an update
    * @param string $table table name as string
    * @param array $data associative array of data and columns
    * @return mixed 
    */
   private function updateOnExist($table, $data)
   {
        $columns    = array();
        $values     = array();
        $upd_values = array();
        foreach ($data as $key => $val) {
            $columns[]    = $this->db->escape_identifiers($key);
            $val = $this->db->escape($val);
            $values[]     = $val;
            $upd_values[] = $key.'='.$val;
        }
        $sql = "INSERT INTO ". $this->db->dbprefix($table) ."(".implode(",", $columns).")values(".implode(', ', $values).")ON DUPLICATE KEY UPDATE".implode(",", $upd_values);
        return $this->db->query($sql);
}

Dav

Answer 7

If the replace solution suggested by Marcos Sánchez Urquiola in his answer does not work for you because you have an autoincrement column, the following is the cleanest way to do it while avoiding having to quote/sanitize the input yourself and letting Codeigniter do that.

$table = 'database.table'; // Usually you would have this in you model
$primary_key = 'id'; // You would also have this in you model

$data = [
    'id' => '14',
    'name' => 'John Smith',
    'email' => '[email protected]',
];

// get keys and remove primary_key if it has been included in the data array
$updates_array = array_filter( array_keys($data), function($fieldName) use ($primary_key) { return $fieldName !== $primary_key; });
array_walk($updates_array, function(&$item){ $item = "{$item}=VALUES({$item})"; } );

$sql = $this->db->insert_string($table, $data) . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates_array);

$this->db->query($sql);

/*
Generates an insert statement with the following at the end:
    ON DUPLICATE KEY UPDATE
        name=VALUES(name),
        email=VALUES(email)
This way you work with the values you already supplied to $this->db->insert_string
*/

Astrea

Answer 8

First, you need to check whether the user or data exists or not. Then you can perform the update and insert operation.

 function yourFuncName($id, $data)
  {
    $this->db->where('user_id', $id);
    $q = $this->db->get('tbl_user');
    if ($q->num_rows() > 0) {
      $this->db->where('user_id', $id);
      $this->db->update('tbl_user', $data);
    } else {
      $this->db->set('user_id', $id);
      $this->db->insert('tbl_user', $data);
    }
  }

Apperception

Answer 9

Updated Milaza's answer - Simply done

    $updt_str = '';
    foreach ($array as $k => $v) {
        $updt_str = $updt_str.' '.$k.' = '.$v.',';
    }
    $updt_str = substr_replace($updt_str,";", -1);
    $this->db->query($this->db->insert_string('table_name', $array).' ON DUPLICATE KEY UPDATE '.$updt_str);

Maynor

Not only the values are not quoted, they are also not sanitized or escaped – Astrea