CODEWITHSUNDEEP
phpcodeigniteractiverecord
Nyxynyx
Nyxynyx

Reputation: 63687

INSERT IGNORE using Codeigniter

I am trying to insert a few rows into the MySQL table using Codeigniter and Active Records.

PHP Code

$data = array('......');  // some rows of data to insert
$this->db->insert_batch('my_table', $data);

However this may cause duplicate rows to be inserted into the table. To handle the insertion of duplicate data, I plan to use the INSERT IGNORE command to not insert the row if the row is a duplicate.

Problem: I cannot find the INSERT IGNORE equivalent in Active Records and do not want to edit the Active Record class. Are there any other alternatives?

The following looks interesting, but if I do the following, wont the query be run twice?

$insert_query = $this->db->insert_batch('my_table', $data);  // QUERY RUNS ONCE
$insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
$this->db->query($insert_query); // QUERY RUNS A SECOND TIME

Upvotes: 28

Views: 44859

Answers (13)

Gulzar Ali
Gulzar Ali

Reputation: 75

Avoid duplicate data insertion in codeigniter is a tricky task. But you can ignore duplicate values insertion using customized library method in very simple and accurate way. I have found a suitable solution at: how-to-avoid-duplicate-data-insertion-in-codeigniter-php

Upvotes: 0

Rahul
Rahul

Reputation: 18567

If still someone is struggling with insert_batch to use ignore,

Check this path,

system/database/DB_query_builder.php

Search for function 

protected function _insert_batch

Change 

INSERT INTO => INSERT IGNORE INTO

Thank me later. :)

Upvotes: 0

asimov
asimov

Reputation: 71

solution :

$sql = $this->db->set($data)->get_compiled_insert($table);
$sql = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
$this->db->query($sql);

works in codeigniter 3.0.6 :)

Upvotes: 1

Avishai Peretz
Avishai Peretz

Reputation: 112

add to file DB_query_builder.php

this 2 functions

public function insert_ignore_batch($table = '', $set = NULL, $escape = NULL) {
    if ($set !== NULL)
    {
        $this->set_insert_batch($set, '', $escape);
    }

    if (count($this->qb_set) === 0)
    {
        // No valid data array. Folds in cases where keys and values did not match up
        return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
    }

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

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

    // Batch this baby
    $affected_rows = 0;
    for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100)
    {
        $this->query($this->_insert_ignore_batch($this->protect_identifiers($table, TRUE, $escape, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100)));
        $affected_rows += $this->affected_rows();
    }

    $this->_reset_write();
    return $affected_rows;
}

protected function _insert_ignore_batch($table, $keys, $values) {
    return 'INSERT IGNORE INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
}

using:

$this->db->insert_ignore_batch('TableName', $data);

Upvotes: 0

Sunny Khanuja
Sunny Khanuja

Reputation: 103

I too had same issue what helped me is :

Open : Codeigniter/system/database/DB_query_builder.php :

find 

protected function _insert_batch($table, $keys, $values)
    {
        return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
    }

and replace with : 

protected function _insert_batch($table, $keys, $values)
{
    return 'INSERT IGNORE INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
}

Upvotes: 0

Jacques Marcotte
Jacques Marcotte

Reputation: 61

This is basically a modification of Rocket Hazmat's suggestion, which is great, but doesn't take into account the fact that str_replace operates on the entire string and might inadvertently affect the data.

$insert_query = $this->db->insert_string('my_table', $data);
$insert_query = preg_replace('/INSERT INTO/','INSERT IGNORE INTO',$insert_query,1);
$this->db->query($insert_query);

Upvotes: 4

Valentin Ghica
Valentin Ghica

Reputation: 11

For this purpose i made this helper function: 

function insert_batch_string($table='',$data=[],$ignore=false){
    $CI = &get_instance();
    $sql = '';

    if ($table && !empty($data)){
        $rows = [];

        foreach ($data as $row) {
            $insert_string = $CI->db->insert_string($table,$row);
            if(empty($rows) && $sql ==''){
                $sql = substr($insert_string,0,stripos($insert_string,'VALUES'));
            }
            $rows[] = trim(substr($insert_string,stripos($insert_string,'VALUES')+6));
        }

        $sql.=' VALUES '.implode(',',$rows);

        if ($ignore) $sql = str_ireplace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
    }
    return $sql;
}

It can do batch insert and batch insert with ignore. To avoid duplicate rows you must set a unique key in the database table for a primary field.

Upvotes: 1

Fakeer
Fakeer

Reputation: 1034

Not highly recommended but here is a hack to preserve the batch insert (which is more efficient wrt Mysql)

// try to insert as usual first
$this->db->insert_batch('my_table', $data);

// if it fails resort to IGNORE
if($this->db->_error_message())
{
        $sql = $this->db->last_query();
        $sql = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
        $this->db->query($sql);
}

Upvotes: 0

sotoz
sotoz

Reputation: 3098

As I also encountered a similar problem, I finally chose a little bit more "elegant" solution like the one below. A complete insert_batch query that is something that uses Rocket's answer AND transactions:

$this->db->trans_start();
foreach ($items as $item) {
       $insert_query = $this->db->insert_string('table_name', $item);
       $insert_query = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insert_query);
       $this->db->query($insert_query);
    }
$this->db->trans_complete();

That will also wrap everything on a transaction resulting on a faster query like doing a insert_batch(). Well not as fast as insert_batch() but faster than a single query for each entry of course. I hope it will help someone.

Upvotes: 8

Algy Taylor
Algy Taylor

Reputation: 834

For batch uploads you might need something more like:

foreach ($data as $data_item) {
    $insert_query = $this->db->insert_string('my_table', $data_item);
    $insert_query = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insert_query);
    $this->db->query($insert_query);
}

UPDATE: Use dcostalis's version (in the comments below this one), it will scale much better :-)

Upvotes: 7

user1117332
user1117332

Reputation: 56

Using the technique of your second idea, you could generate a query by looping over the array and using:

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

Upvotes: 3

gen_Eric
gen_Eric

Reputation: 227280

Don't use insert_batch, as it actually runs the query. You want insert_string

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

UPDATE: This doesn't work for batch queries, only one row at a time.

Upvotes: 46

phirschybar
phirschybar

Reputation: 8579

Avoid duplicate rows by setting a unique key in the database table for at least one of the fields.

Upvotes: -1

Related Questions