How to insert records using select in codeigniter active record

Question

I want to implement a sql query using CodeIgniter Active Record class. The query looks like this..

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

Is this possible in CodeIgniter without using the $this->db->query method?

Solution:

$this->db->select('au_id, au_lname, au_fname');
$this->db->from('california_authors');
$this->db->where('state', 'CA');
$query = $this->db->get();

if($query->num_rows()) {
    $new_author = $query->result_array();

    foreach ($new_author as $row => $author) {
        $this->db->insert("authors", $author);
    }           
}

Regards

Edgar Nadal · Accepted Answer · 2010-07-29 12:56:08Z

12

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 */

answered Jul 29, 2010 at 12:56

Edgar Nadal

3552 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vikmalhotra Over a year ago

I think there needs to be a foreach as well inside the if($select->num_rows())

Msd.Abd · Accepted Answer · 2020-12-02 11:16:57Z

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\'');

Vincent · Accepted Answer · 2020-11-07 12:53:21Z

0

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_fname
FROM authors
WHERE State = ?
";
$this->db->query($sql, array($state));

codeigniter-3.2.1

answered Nov 7, 2020 at 12:53

Vincent

543 bronze badges

Collectives™ on Stack Overflow

How to insert records using select in codeigniter active record

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related