How do you insert multiple records into a table at

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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 !

Answer 5

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