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.

Solution 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

Solution 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.

Solution 3

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();

Comments

[Rohan Desai]

This doesn't look like Javascript. I think it is PHP.

[Pigalev Pavel]

Thanks! I've forgot to change my answer here but in my project I also end up making a new class. In a bit different way but very alike. I've updated my answer.

[kouton]

What about escaping values?

[Pigalev Pavel]

@kouton can you be more specific?

[kouton]

My bad, didn't notice the param binding in the loop.

[Esamo]

This throws SQL error when number of columns is different for some rows.

[Nitin]

you can make it positive response