How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?

Question

I have a CodeIgniter/PHP Model and I want to insert some data into the database.

However, I have this set in my 'raw' SQL query:

ON DUPLICATE KEY UPDATE duplicate=duplicate+1

I am using CodeIgniter and am converting all my previous in-controller SQL queries to ActiveRecord. Is there any way to do this from within the ActiveRecord-based model?

Thanks!

Jack

A relevant snippet with attention to secure querying practices here: https://codereview.stackexchange.com/a/252785/141885 — mickmackusa, Nov 28 '20 at 13:25

Shaffe · Answer 1 · 2016-12-18T23:23:02.730

57

You can add the "ON DUPLICATE" statement without modifying any core files.

$sql = $this->db->insert_string('table', $data) . ' ON DUPLICATE KEY UPDATE duplicate=LAST_INSERT_ID(duplicate)';
$this->db->query($sql);
$id = $this->db->insert_id();

I hope it's gonna help someone.

edited Dec 18 '16 at 23:23

answered Apr 04 '13 at 16:03

Shaffe

1,265
13
11

3

Since this is a top result on google for "insert on duplicate key" it would be unwise not to extend this answer to cater for those who just wish that on duplicate key inserts and returns the 'id' on duplicate. => ON DUPLICATE KEY UPDATE duplicate=LAST_INSERT_ID(duplicate) – Kennedy Nyaga Feb 12 '15 at 07:44

score 6 · Answer 2 · answered May 10 '16 at 17:42

Following the snippet linked by Pickett, I made a few modifications to:

1) Update it to use the new Query Builder (CI 3), formerly known as Active Record.

2) You can now pass an associative array (key => value) or an array of associative arrays. In the second form, it performs a multi-update.

I only tested it with mysqli, and it works well.

This piece of code goes in system/database/drivers/mysqli/mysqli_driver.php

function _duplicate_insert($table, $values)
{
    $updatestr = array();
    $keystr    = array();
    $valstr    = array();

    foreach($values as $key => $val)
    {
        $updatestr[] = $key." = ".$val;
        $keystr[]    = $key;
        $valstr[]    = $val;
    }

    $sql  = "INSERT INTO ".$table." (".implode(', ',$keystr).") ";
    $sql .= "VALUES (".implode(', ',$valstr).") ";
    $sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);

    return $sql;
}

function _multi_duplicate_insert($table, $values)
{
    $updatestr = array();
    $keystr    = array();
    $valstr    = null;
    $entries   = array();

    $temp = array_keys($values);
    $first = $values[$temp[0]];

    foreach($first as $key => $val)
    {
        $updatestr[] = $key." = VALUES(".$key.")";
        $keystr[]    = $key;
    }

    foreach($values as $entry)
    {
        $valstr = array();
        foreach($entry as $key => $val)
        {
            $valstr[] = $val;
        }
        $entries[] = '('.implode(', ', $valstr).')';
    }

    $sql  = "INSERT INTO ".$table." (".implode(', ',$keystr).") ";

    $sql .= "VALUES ".implode(', ',$entries);
    $sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);

    return $sql;
}

And this goes into the /system/database/DB_query_builder.php file:

function on_duplicate($table = '', $set = NULL )
{
    if ( ! is_null($set))
    {
        $this->set($set);
    }

    if (count($this->qb_set) == 0)
    {
        if ($this->db_debug)
        {
            return $this->display_error('db_must_use_set');
        }
        return FALSE;
    }

    if ($table == '')
    {
        if ( ! isset($this->qb_from[0]))
        {
            if ($this->db_debug)
            {
                return $this->display_error('db_must_set_table');
            }
            return FALSE;
        }

        $table = $this->qb_from[0];
    }

    $is_multi = false;
    foreach (array_keys($set) as $k => $v) {
        if ($k === $v) {
            $is_multi = true; //is not assoc
            break;
        }
    }

    if($is_multi)
    {
        $sql = $this->_multi_duplicate_insert($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_set );
    }
    else
    {
        $sql = $this->_duplicate_insert($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_set );
    }

    $this->_reset_write();
    return $this->query($sql);
}

Then you can do this for a single row insert/update:

$this->db->on_duplicate('table', array('column1' => 'value', 'column2' => 'value'));

Or this for a multi insert/update:

$this->db->on_duplicate('table', array(
    array('column1' => 'value', 'column2' => 'value'),
    array('column1' => 'value', 'column2' => 'value')
));

This would be great for the [official repo](https://github.com/bcit-ci/CodeIgniter). — Chofoteddy, Jun 22 '18 at 21:40

score 6 · Answer 3 · answered Jun 18 '16 at 00:40

6

The below process work for me in Codeigniter 3.0.6

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, $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();
}

answered Jun 18 '16 at 00:40

Amalesh

61
1
1

this work for me. Just copy and paste this code to model file and call from controller just like other function. – dewaz Jul 31 '20 at 12:33
this code causes a serious security issue. you really need to sanitize `$value` and possibly `$key` as well. or change it to use bind – mask8 Jun 22 '22 at 18:29

Andrej Burcev · Answer 4 · 2013-03-05T00:12:46.070

You can tweak the active record function with minimal addition:

DB_driver.php add inside the class:

protected $OnlyReturnQuery = false;
public function onlyReturnQuery($return = true)
{
    $this->OnlyReturnQuery = $return;
}

find function query( ...and add at the very beginning:

    if ($this->OnlyReturnQuery) {
        $this->OnlyReturnQuery = false;
        return $sql;
    }

and finally in DB_active_rec.php add function:

public function insert_or_update($table='', $data=array())
{
    $this->onlyReturnQuery();
    $this->set($data);
    $insert = $this->insert($table);
    $this->onlyReturnQuery();
    $this->set($data);
    $update = $this->update($table);
    $update = preg_replace('/UPDATE.*?SET/',' ON DUPLICATE KEY UPDATE',$update);
    return $this->query($insert.$update);
}

Now you can use it as:

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

Pros: uses all the active record validation Cons: it is not the best (the most proper) way of extending the function, because if you are planning to update these files, you will have to redo the procedure.

score 2 · Answer 5 · edited Jul 26 '17 at 11:58

2

Below is a code snippet I use everytime for update on duplicates:

$sql = "insert into  table_name (id, name) values(".$id.",'".$name."') on duplicate key update id=".$id.",name='".$name."'";
//Executing queries
$result = $this->db->query($sql, array($id, $name));

Note: column id must be primary or unique

edited Jul 26 '17 at 11:58

Anbuselvan Rocky

606
6
22

answered Jul 18 '16 at 11:24

Anmol Mourya

530
5
7

score 2 · Answer 6 · answered May 03 '11 at 18:13

The link to the forum thread above is broken. I don't know of a better way than just using db->query for the call, if someone has a better solution, please post that.

$result = $this->CI->db->query(
    "INSERT INTO tablename (id, name, duplicate) VALUES (1, 'Joe', 1) ".
    "ON DUPLICATE KEY UPDATE duplicate=duplicate+1");

I hope this helps someone looking for a solution to this.

score 0 · Answer 7 · answered Dec 05 '17 at 07:21

Using $this->db->query() and parameters, this is how I do it. the first 4 parameters are for the insert part and the last three parameters are repeated for the on duplicate key update part.

$sql = "insert into application_committee ".
       "(application_id, member1_id, member2_id, member3_id) values (?, ?, ?, ?)".
       " on duplicate key update member1_id = ?, member2_id = ?, member3_id = ?";

$this->db->query($sql, array($appid, $member_1, $member_2, $member_3, 
                 $member_1, $member_2, $member_3);

score 0 · Answer 8 · answered Sep 28 '18 at 07:01

Simply done -

    $updt_str = '';
    foreach ($insert_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', $insert_array).' ON DUPLICATE KEY UPDATE '.$updt_str);

How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?

8 Answers8

Linked

Related