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

Answer 1

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.

Answer 2

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

Answer 3

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.