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

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_typ开发者_C百科e, 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?

---

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.

---

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 !

---

foreach($facilities as $facility)
{ 
    $model = new Model;
    $model->attributes = $facility // or $model->column = $facility
    if ($model->validate())
        $model->save() 
}

---

//only for 1.1.14
$builder = Yii::app()->db->schema->commandBuilder;
$command=$builder->createMultipleInsertCommand('tbl_post', array(
  array('title' => 'record 1', 'text' => 'text1'),
  array('title' => 'record 2', 'text' => 'text2'),
));
$command->execute();

http://www.yiiframework.com/news/72/yii-1-1-14-release-candidate-is-available/

---

Since your question is tagged in "yii" I guess you are using Yii Framework. Take a look at Active Records over at the docs - http://www.yiiframework.com/doc/guide/1.1/en/database.ar

Follow the docs to set up AR classes for your tables, and simply loop over the data you post when you submit your checkboxlist. In this loop you create, populate and save the AR objects for the tables you wish to insert data for.