Insert multiple rows at a time in YII

Question

I have gone through the http://www.yiiframework.com/doc/guide/1.1/en/form.table

I did not understand what this means:

$items=$this->getItemsToUpdate();

what is this $this->getItemsToUpdate() function?

I am trying to insert dynamic rows at a time. I have used jquery for creating the data but I don't know how to insert them into the database.

Answer 1

Yii supports to add multiple records.

Use this....

 $alertStatus[] = array(
                        'db_field_name1' => $value1,
                        'db_field_name1' => $value2, 
                        'created_on' => new CDbExpression('NOW()'),
                        'modified_on' => new CDbExpression('NOW()')
                    );
 $connection = Yii::app()->db->getSchema()->getCommandBuilder();
 $command = $connection->createMultipleInsertCommand('table_name', $alertStatus);
            $command->execute();

$alertStatus array should contains all the fields of database

Answer 2

I've faced the same issue.

As far as I know Yii doesn't have default function to insert multiple rows.

So I've created one in CdbCommand class (framework/db/CDbCommand.php) :

/**
 * Creates and executes an INSERT SQL statement for several rows.
 * @param string $table the table that new rows will be inserted into.
 * @param array $array_columns the array of column datas array(array(name=>value,...),...) to be inserted into the table.
 * @return integer number of rows affected by the execution.
 */
public function insertSeveral($table, $array_columns)
{
    $sql = '';
    $params = array();
    $i = 0;
    foreach ($array_columns as $columns) {
        $names = array();
        $placeholders = array();
        foreach ($columns as $name => $value) {
            if (!$i) {
                $names[] = $this->_connection->quoteColumnName($name);
            }
            if ($value instanceof CDbExpression) {
                $placeholders[] = $value->expression;
                foreach ($value->params as $n => $v)
                    $params[$n] = $v;
            } else {
                $placeholders[] = ':' . $name . $i;
                $params[':' . $name . $i] = $value;
            }
        }
        if (!$i) {
            $sql = 'INSERT INTO ' . $this->_connection->quoteTableName($table)
                . ' (' . implode(', ', $names) . ') VALUES ('
                . implode(', ', $placeholders) . ')';
        } else {
            $sql .= ',(' . implode(', ', $placeholders) . ')';
        }
        $i++;
    }
    return $this->setText($sql)->execute($params);
}

Usage:

$rows = array(
            array('id' => 1, 'name' => 'John'),
            array('id' => 2, 'name' => 'Mark')
);
$command = Yii::app()->db->createCommand();
$command->insertSeveral('users', $rows);

---

Update

As Nabi K.A.Z. mentioned it is really much better not to touch source code of framework. In my project I've actually created class MyCDbCommand (that extends CDbCommand) and I've created MyCDBConnection (that extends CDbConnection).

MyCDbCommand (extending CDbCommand):

class MyCDbCommand extends CDbCommand
{
    protected $_connection;

    public function __construct(CDbConnection $connection, $query = null)
    {
        $this->_connection = $connection;
        parent::__construct($connection, $query);
    }

    /**
     * Creates and executes an INSERT SQL statement for several rows.
     * @param string $table the table that new rows will be inserted into.
     * @param array $array_columns the array of column datas array(array(name=>value,...),...) to be inserted into the table.
     * @return integer number of rows affected by the execution.
     */
    public function insertSeveral($table, $array_columns)
    {
        $sql    = '';
        $params = array();
        $i      = 0;
        foreach ($array_columns as $columns) {
            $names        = array();
            $placeholders = array();
            foreach ($columns as $name => $value) {
                if (!$i) {
                    $names[] = $this->_connection->quoteColumnName($name);
                }
                if ($value instanceof CDbExpression) {
                    $placeholders[] = $value->expression;
                    foreach ($value->params as $n => $v) {
                        $params[$n] = $v;
                    }
                } else {
                    $placeholders[]           = ':' . $name . $i;
                    $params[':' . $name . $i] = $value;
                }
            }
            if (!$i) {
                $sql = 'INSERT INTO ' . $this->_connection->quoteTableName($table)
                    . ' (' . implode(', ', $names) . ') VALUES ('
                    . implode(', ', $placeholders) . ')';
            } else {
                $sql .= ',(' . implode(', ', $placeholders) . ')';
            }
            $i++;
        }
        return !empty($sql) ? $this->setText($sql)->execute($params) : 0;
    }

}

MyCDBConnection (extending CDbConnection and using MyCDbCommand):

class MyCDbConnection extends CDbConnection
{

    public function createCommand($query = null)
    {
        $this->setActive(true);
        return new MyCDbCommand($this, $query);
    }
}

Then I've changed config file (/protected/config/main.php). I've changed CDbConnection to MyCDbConnection there:

...
'components'=>array(
    ...
    'db' => array(
        'connectionString' => 'mysql:host=localhost;dbname=dname',
        'username'         => 'user',
        'password'         => 'password',
        'charset'          => 'utf8',
        'class'            => 'MyCDbConnection', // Change default CDbConnection class to MyCDbConnection
    ),
    ...
    )
...

And here you go. We did it without touching a core framework and you can use it the same way as before.

Answer 3

The above code from Pigalev Pavel was great.

But that need to modify core framework and this is bad!

So, I write a independent class.

Put this code in components folder under GeneralRepository.php file name.

<?php
class GeneralRepository
{
    /**
     * Creates and executes an INSERT SQL statement for several rows.
     * 
     * Usage:
     * $rows = array(
     *      array('id' => 1, 'name' => 'John'),
     *      array('id' => 2, 'name' => 'Mark')
     * );
     * GeneralRepository::insertSeveral(User::model()->tableName(), $rows);
     * 
     * @param string $table the table that new rows will be inserted into.
     * @param array $array_columns the array of column datas array(array(name=>value,...),...) to be inserted into the table.
     * @return integer number of rows affected by the execution.
     */
    public static function insertSeveral($table, $array_columns)
    {
        $connection = Yii::app()->db;
        $sql = '';
        $params = array();
        $i = 0;
        foreach ($array_columns as $columns) {
            $names = array();
            $placeholders = array();
            foreach ($columns as $name => $value) {
                if (!$i) {
                    $names[] = $connection->quoteColumnName($name);
                }
                if ($value instanceof CDbExpression) {
                    $placeholders[] = $value->expression;
                    foreach ($value->params as $n => $v)
                        $params[$n] = $v;
                } else {
                    $placeholders[] = ':' . $name . $i;
                    $params[':' . $name . $i] = $value;
                }
            }
            if (!$i) {
                $sql = 'INSERT INTO ' . $connection->quoteTableName($table)
                . ' (' . implode(', ', $names) . ') VALUES ('
                . implode(', ', $placeholders) . ')';
            } else {
                $sql .= ',(' . implode(', ', $placeholders) . ')';
            }
            $i++;
        }
        $command = Yii::app()->db->createCommand($sql);
        return $command->execute($params);
    }
}

And usage anywhere:

$rows = array(
    array('id' => 1, 'name' => 'John'),
    array('id' => 2, 'name' => 'Mark')
);
GeneralRepository::insertSeveral(User::model()->tableName(), $rows);

Answer 4

If you're not constrained to working with Active Records, you could use DAO to execute an appropriate SQL insert statement for inserting multiple rows. You can find an example of such a statement here:

Insert multiple records into MySQL with a single query

Once you have your insert statement as a string (which can be constructed dynamically using a loop in case you don't know in advance how many rows you need to insert), you can execute it like this:

$sql = 'INSERT statement goes here';
$connection = Yii::app() -> db;
$command = $connection -> createCommand($sql);
$command -> execute();