Adding "INSERT IGNORE" to Active Record by Extending the CI Core Rather than Hacking the CI Core

[eluser]Unknown[/eluser]
Initially, I used the method for adding INSERT IGNORE posted here: http://christiesmedia.co.uk/blog/3-blog/...ert-ignore

But hacking the core files didn't sit well with me so I combined it with this method for extending database drivers: http://codeigniter.com/wiki/Extending_Database_Drivers/

I also had to make some small changes in MY_Loader.php to make it work with <b>CI 2.0</b>.

To use it, place <b>MY_Loader.php</b> in <b>application/core/</b> and <b>MY_DB_mysql_driver.php</b> in <b>application/libraries/</b>.

If you want to use it with <b>1.7.2</b> (not tested), you should copy the database function from the second tutorial above, exactly as is into MY_Loader.php, replacing my version of database(). You'll also have to place MY_Loader.php into application/libraries/ rather than application/core/. But again, I haven't tested it with 1.7.2 so please report back.

Use it like this:

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

$this->db->ignore();
$this->db->insert('mytable', $data);

// Produces: INSERT IGNORE INTO mytable (ID,title, name, date) VALUES ('ID','My title', 'My name', 'My date')

Good luck! And thank you to the authors of the two tutorials!

[eluser]Gabriel Labrecque[/eluser]
Thanks a bunch, I was looking for this piece of code!

One thing though, I saw that the article on christiesmedia.co.uk says to modify the _reset_write() method, not the _reset_select() one. The code you provided in application.zip overwrites the _reset_select() method. I guess the method should be replaced by the following:

Code:

function _reset_write()
{
    $ar_reset_items = array(
        'ar_set'     => array(),
        'ar_from'    => array(),
        'ar_where'   => array(),
        'ar_like'    => array(),
        'ar_orderby' => array(),
        'ar_limit'   => FALSE,
        'ar_order'   => FALSE,
        'ar_ignore'  => FALSE
    );
    
    $this->_reset_run($ar_reset_items);
}

Also, one might need to IGNORE UPDATEs as well. I suggest adding the following to your code:

Code:

function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
{
    foreach($values as $key => $val)
    {
        $valstr[] = $key." = ".$val;
    }
    
    $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
    
    $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
    
    $sql = ( ! $this->ar_ignore) ? 'UPDATE ' : 'UPDATE IGNORE ';
    $sql .= $table." SET ".implode(', ', $valstr);
    
    $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
    
    $sql .= $orderby.$limit;
    
    return $sql;
}

[eluser]Unknown[/eluser]
Thanks for the suggestions! Here's an updated version which includes UPDATE IGNORE.
-Thom

[eluser]Unknown[/eluser]
This would be very useful for me tonight, but for some reason I cannot download the attachment... The forum just gives me a white screen, am I the only one?

[eluser]Freeze Dried Pop[/eluser]
Same here, could do with being able to download this.

[eluser]Freeze Dried Pop[/eluser]
Figured something out for myself in the end:

First extend the CI_Loader by creating a MY_Loader.php file in the "core" folder containing the following: (courtesy of Simon Emms)

Code:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* MY_Loader
*
* @author Simon Emms <[email protected]>
*/
class MY_Loader extends CI_Loader {

    /**
     * Database Loader
     *
     * @access    public
     * @param    string    the DB credentials
     * @param    bool    whether to return the DB object
     * @param    bool    whether to enable active record (this allows us to override the config setting)
     * @return    object
     */
    function database($params = '', $return = FALSE, $active_record = NULL)
    {
        // Grab the super object
        $CI =& get_instance();

        // Do we even need to load the database class?
        if (class_exists('CI_DB') AND $return == FALSE AND $active_record == NULL AND isset($CI->db) AND is_object($CI->db)) {
            return FALSE;
        }

        require_once(BASEPATH.'database/DB'.EXT);

        // Load the DB class
        $db =& DB($params, $active_record);

        $my_driver = config_item('subclass_prefix').'DB_'.$db->dbdriver.'_driver';
        $my_driver_file = APPPATH.'core/'.$my_driver.EXT;

        if (file_exists($my_driver_file)) {
            require_once($my_driver_file);
            $db = new $my_driver(get_object_vars($db));
        }

        if ($return === TRUE) {
            return $db;
        }

        // Initialize the db variable.  Needed to prevent
        // reference errors with some configurations
        $CI->db = '';
        $CI->db = $db;
    }
}
?&gt;

Then create a file called "MY_DB_mysql_driver.php" in the "core" folder and put the following:

Code:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* MY_DB_mysql_driver
*
* @author Rob Brain <twitter.com/freezedriedpop>
*/
class MY_DB_mysql_driver extends CI_DB_mysql_driver {
/**
     * Insert Ingore
     *
     * What this basically does is compile a query as an INSERT and then simply
  * amend the string "INSERT" at the start of the query to be "INSERT IGNORE".
  *
  * INSERT IGNORE means that any errors for duplicate rows will be suppressed to
  * warnings.
     *
     * @access    public
     * @param    string    the table name
     * @param    string    optional insert data
     * @return    object
     */
function insert_ignore($table, $data = FALSE)
{
  if($data) $this->set($data);

  $query_string = 'INSERT IGNORE'.substr($this->get_compiled_insert($table), 6);

  return $this->query($query_string);
}
}
?&gt;

I don't know if others will agree on it being the best way to do things, but it's the quickest and simplest. All it does is rather than run an insert, it just compiles it and then replaces the string "INSERT" with "INSERT INTO".