How do you insert multiple records into a table at the same time?

Question

I have two tables Accommodation and Facility, which are connected in a many-to-many relationship with a third table, Accommodation_facility.

• Accommodation (accommodation_id, accommodation_type, name)
• Facility (facility_id, facility_name)
• Accommodation_facility (accommodation_id, facility_id)

Using Yii, how can you insert multiple records of data into the Accomodation_facility table?

Solution 1

Inserting using a loop is very slow. Let's say you have 5000 rows to insert, it's going to take around 6 minutes that way (separate insert for each record). It's better to insert the data with a single query:

$values = '(null, "your string"), (null, "next string"), (null, "third string")';
$sql = 'INSERT INTO table_data (id, data) VALUES ' . $values;
$command = Yii::app()->db->createCommand($sql);
$command->execute();

That will take 1/10 of the time.

Solution 2

You better have to use bindParam to prevent from SQL injections. I don't know if it is the best way to do that, but there is the way i'm doing this :

$values = array(array(1,2),array(3,4),array(5,6),);
$nbValues = count($values);
$sql = 'INSERT INTO table_name (col_name1, col_name2) VALUES ';
for ($i=0; $i < $nbValues; $i++) { 
    $sql .= '(:col1_'.$i.', :col2_'.$i.')';
    if ($i !== ($nbValues-1))
        $sql .= ',';
}
$command = Yii::app()->db->createCommand($sql);
for ($i=0; $i < $nbValues; $i++) { 
    $command->bindParam(':col1_'.$i, $values[$i][0], PDO::PARAM_INT);
    $command->bindParam(':col2_'.$i, $values[$i][1], PDO::PARAM_INT);
}
$command->execute();

Hope this helps !

Comments

[SohailRajput]

in your case only one record will be inserted. probably last one.

[SohailRajput]

There should be $model->isNewRecord = true; in order to add multiple record in the loop.

[FelikZ]

The better expirience is to use $command->bindValues and placeholders then raw data to prevent SQL injection.