Insert multiple rows at once with CodeIgniter's active record method

Question

Ok, so I have following SQL query:

INSERT INTO film_genre
    (film_id, genre_id)
VALUES
    (1, 1),
    (1, 2),
    (1, 3)

I need to convert this into CodeIgniter's active record syntax (CI2) and this is what I have so far, but I only get one data entry out of three:

$movie_genre = array(
    'film_id' => $movies['id'],
    'genre_id' => 1, 
    'film_id' => $movies['id'],
    'genre_id' => 2, 
    'film_id' => $movies['id'],
    'genre_id' => 3, 
);
    
$this->db->insert('film_genre', $movie_genre );

Answer 1

If you execute a print_r($movie_genre) before your insert you will see that there is just a single value for film_id and genre_id. This is because there can be only one value for each key in an array. You are defining three values for each key, so your earlier values are getting overwritten.

If you format your array as follows, you can use the insert_batch method. See a full description of the method at http://ellislab.com/codeigniter/user-guide/database/active_record.html

$records = array(
    array('film_id' => 1, 'genre_id' => 1),
    array('film_id' => 1, 'genre_id' => 2),
    array('film_id' => 1, 'genre_id' => 3)
);

$this->db->insert_batch('film_genre', $records);