My tweak for multiple database insertion with AR

[eluser]J.Frederic[/eluser]
Hello everyones mirk:


I want share with you my CI tweak to insert more than one row using the native CI AR class.

usually it's something like that

Code:

$table = 'news';
$data[title] = 'My tweak for multiple database insertion with AR';
$data[text] = 'Hello everyones ... ...';
$this->db->insert($table,$data);

but for some reason, i actually need to use this way :

Code:

$table = 'news';
//first row
$data[0][title] = 'title 1';
$data[0][text] = 'text 1';
//second row
$data[1][title] = 'title 2';
$data[1][text] = 'text 2';
$this->db->insert($table,$data);

So, to make that possible, because is not possible to extend the database class of CI, I had no other choice to hack the native datebase code. I had try to make it clean. Feel free to give me your opinions and/or suggestions.

hack 1 : system/database/DB_active_rec.php | function insert | line(1170)
--------------------------------------------------------------------------

Code:

function insert($table = '', $set = NULL)
    {    
        
        if ( ! is_null($set))
        {
            $this->set($set);
        }
    
        if (count($this->ar_set) == 0)
        {
            if ($this->db_debug)
            {
                return $this->display_error('db_must_use_set');
            }
            return FALSE;
        }

        if ($table == '')
        {
            if ( ! isset($this->ar_from[0]))
            {
                if ($this->db_debug)
                {
                    return $this->display_error('db_must_set_table');
                }
                return FALSE;
            }
            
            $table = $this->ar_from[0];
        }
        /* HACK JF #65
            call my new _insertArray method if it is a multidimentional array
        */
        if(is_array($set[0])){
            
            $sql = $this->_insertArray($this->_protect_identifiers($table, TRUE, NULL, FALSE),array_keys($this->ar_set[0]),$this->ar_set);
        }else{
            /*original*/
            $sql = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));
        }
        /*END OF HACK JF #65*/
        
        $this->_reset_write();
        return $this->query($sql);        
    }

hack 2 : system/database/DB_active_rec.php | function set| line(999)
--------------------------------------------------------------------------

Code:

function set($key, $value = '', $escape = TRUE)
    {
        
        $key = $this->_object_to_array($key);
        
        /* HACK JF #65 */
        /* work on the sublevel array if its a multidimentional and add index to $this->ar_set */
        if (is_array($key[0]))
        {
            $i = 0;
            foreach ($key as $v){
                foreach($v as $kk=>$vv){
                    $this->ar_set[$i][$this->_protect_identifiers($kk)] = $this->escape($vv);
                }
                $i++;
            }
            return $this;
        }
        /* end of HACK JF #65 */
        
        
    
        if ( ! is_array($key))
        {
            
            $key = array($key => $value);
        
        }    

        foreach ($key as $k => $v)
        {
            if ($escape === FALSE)
            {
                $this->ar_set[$this->_protect_identifiers($k)] = $v;
            }
            else
            {
                $this->ar_set[$this->_protect_identifiers($k)] = $this->escape($v);
            }
        }
        
        return $this;
    }

hack 3 : system/database/drivers/mysql/mysql_driver.php | new function _insertArray| add after _insert()
--------------------------------------------------------------------------

Code:

function _insertArray($table, $keys, $values)
    {    
        $query = "INSERT INTO ".$table." ";
        $query .= " (".implode(', ', $keys).") ";
        $query .= " VALUES ";
        foreach ($values as $v){
            $query .= " ( ";
            $query .= implode(', ', $v);
            $query .= " ),";
        }
        return substr($query,0,-1); //delete the last ","
    }

Its that, feel free if you have any questions.

JF

[eluser]pbreit[/eluser]
I'd avoid hacking up core for that. I'd suggest just programming the loop in your own code. If you want to keep it DRY, create a helper or something.

[eluser]J.Frederic[/eluser]
Maybe, but for me a loop is worst

i mean, du to the number of insertio that i need to manage.

[eluser]pbreit[/eluser]
For me, hacking the core is the worst.

If you are going to routinely do bulk inserts, I can possibly understand your approach.

[eluser]Dennis Rasmussen[/eluser]
I don't understand why you would want to hack the core system with something you could do in a helper, model or even a library.
If you need to add more data in a single query, just use $this->db->query() - which you basically are doing in hack 1.

Try not to mess with the core system, specially not when it isn't necessary.

[eluser]J.Frederic[/eluser]
You are right , hacking the core is not the best pratice.

But i cant say that is a mess.

I just finish an abstraction layer between my model db class and CI db class too manage other way my need. And i clean my modif in the core.

but, its could be a good idea that CI in a futur realise manage multi row insertion.

[eluser]n0xie[/eluser]
I solved this by adding a method to MY_Model which basically does a bulk insert:

Code:

function insert_multiple($data)
    {
      $values = NULL;
      $count = count($data)-1;
      
      for($i=0; $i<=$count; $i++)
      {
        $values.= "('".implode("','",str_replace("'", " ", $data[$i]))."')";
        if ($i < $count)
        {
          $values.=",";
        }
      }
      $sql = "INSERT INTO $this->_table (".implode(',',array_keys($data[0])).") VALUES ".$values;
      $query = $this->db->query($sql);
    }

[eluser]J.Frederic[/eluser]
Good Function,

Really look like my new one. But i still continue to call by the same way

I mean, I just replace $this-­­>db by $this->myDB in my code.

myDB is just an abastract layer who call for now $this->db methods except when the function insert() receive a multidimentionnal array. In this case it call the myDB special BULK function like yours.

For optimise the bulk function, i add 2 functions like the native CI database have,

protect_identifiers(){.... //for the Key
escape(){ // for the value