EllisLab text mark
Advanced Search
     
ActiveRecord Question
 
RobertB.
Posted: 06 September 2012 10:37 PM   [ Ignore ]
Avatar
Joined: 2010-02-19
73 posts

How can I do this with active record

Objective

Insert “Duplicate” all the content of a single column in a table to a column of another table.

This works

INSERT INTO tbl2 (`column tbl2`) 
SELECT column tbl1
FROM   tbl1 

This does not work

$this -> db -> insert('tbl2', ('column tbl2'));
$this -> db -> select('column tbl1');
$this -> db -> from('tbl1'); 

Thanks.
 
codemonkey
Posted: 07 September 2012 05:32 PM   [ Ignore ]   [ # 1 ]   [ Rating: 0 ]
Joined: 2012-04-23
41 posts

Even though this was hard to read, I would attack this like this.

Pull the data from the column and store it in a varible. Run another query to insert the varible data in the other column.
 
Aken
Posted: 07 September 2012 05:47 PM   [ Ignore ]   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2007-11-28
2435 posts

Active Record doesn’t support that. You would have to write the SELECT query in the insert value’s spot, but AR might auto-escape it.

You can try it - if it doesn’t work, you’ll need to do it manually (this is untested, just guessing off my memory):

$this->db->insert('table', array('column' => 'SELECT column FROM table2')); 
 
RobertB.
Posted: 09 September 2012 12:24 PM   [ Ignore ]   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2010-02-19
73 posts

Thanks for taking the time.

This is the only way that I have being able to make it work. As you can see I have to use the dbprefix in the query.
This way inserts all the ids of table 1 into a column in table 2.

$this -> db -> query("INSERT INTO dbprefix_tbl2 (tbl2.tbl1_id) SELECT tbl1.id FROM dbprefix_tbl1;"); 

The way that you suggest only insert 1 record.

$this -> db -> insert('tbl2', array('tbl2.tbl1_id' => 'SELECT tbl1.id FROM tbl1')); 

Thanks
 
arnab
Posted: 12 September 2012 01:47 AM   [ Ignore ]   [ # 4 ]   [ Rating: 0 ]
Joined: 2012-09-12
1 posts

thanks a lot
I have been trying to do this for several days.
But now its awesome