How to insert records using select in codeigniter active record
I think you are talking about a a SELECT ... INSERT query, on the active record class there is not a method to do that, but there are two ways to do it
1)
$query = $this->db->query('INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = \'CA\'');
As you say
And 2) you can can do this, using what Calle said,
$select = $this->db->select('au_id, au_lname, au_fname')->where('state', 'CA')>get('california_authors');if($select->num_rows()){ $insert = $this->db->insert('california_authors', $select->result_array());}else{ /* there is nothing to insert */
it's an old post but this may be usefull to someone.
It's the same as Edgar Nadal answer, with safer way to pass parameter to query
$state = 'CA';$sql = "INSERT california_authors (au_id, au_lname, au_fname)SELECT au_id, au_lname, au_fnameFROM authorsWHERE State = ?";$this->db->query($sql, array($state));
codeigniter-3.2.1
if you want to have good control on query execution then you could do your SELECT ... INSERT in 3 ways:
1)use codeigniter active record insert_batch(ci3) or insertBatch(ci4)(recomended):
$select = $this->db->select('au_id, au_lname, au_fname')->where('state','CA')>get('california_authors');if($select->num_rows()){ $insert = $this->db->insert_batch('california_authors', $select->result_array());}else{ /* there is nothing to insert */}
2)use codeigniter active record simple insert:
$select = $this->db->select('au_id, au_lname, au_fname')->where('state','CA')>get('california_authors');if($select->num_rows()){ foreach($select->result_array() as $row) $this->db->insert('california_authors', $row);}else{ /* there is nothing to insert */}
3)use codeigniter active record query execution:
$query = $this->db->query('INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = \'CA\'');