CodeIgniter Active Record insert from one table to another

5
votes

What is the syntax for inserting data from one table to another table, using codeigniter active record syntax? I tried the usual mysqli query and it works, but I want to use CodeIgniter Active Record syntax for consistency.

I tried playing with these CodeIgniter Active Record queries but still no luck: 

function insert_into()  
{    
    $this->db->insert('table1');
    $this->db->set('to_column');  
    $this->db->select('from_column');
    $this->db->from('table2');
}
2
phpmysqlcodeigniter
what do you want here? select from 1 table then insert in another? is it? – Muhammad Raheel
yes raheel, thats what im trying to achieve. – swordfish
below answer is right you can do it like this – Muhammad Raheel

2 Answers

14
votes

I think the best thing to accomplish that is fetching the data you want from one table using the get method, then using one of the query results grabber functions (like result() ), iterate over the rows one by one using the insert() method.

Putting this in code:

$query = $this->db->get('table1');
foreach ($query->result() as $row) {
      $this->db->insert('table2',$row);
}

Of course, i suppose that table1 has exactly th same structure as table2 (the same column names and data types for each column). If that is not the case, you will have to map the columns from one table to the another using assignments, but if that is the case your code will be more wide.

4
votes

Copies $source_table into $archive_table:

if(($this->db->table_exists($source_table) && ($this->db->table_exists($archive_table)){
    if ($this->db->query("CREATE TABLE $archive_table LIKE $source_table")){
        if($this->db->query("INSERT $archive_table SELECT * FROM $source_table")){
           echo 'copied ok';
        }
     } 
}

Neater than looping over the entire result set and inserting one row at a time.