$items=$this->getItemsToUpdate();
这个 $this->getItemsToUpdate()
函数是什么?
我正在尝试一次性插入动态行。我已经使用jquery创建了数据,但我不知道如何将它们插入到数据库中。
$items=$this->getItemsToUpdate();
这个 $this->getItemsToUpdate()
函数是什么?
我正在尝试一次性插入动态行。我已经使用jquery创建了数据,但我不知道如何将它们插入到数据库中。
Yii支持添加多个记录。
使用以下代码....
$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数组应包含数据库中的所有字段。
我曾经遇到过相同的问题。
据我所知,Yii没有默认函数来插入多行数据。
因此,我在CdbCommand
类(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);
}
使用方法:
$rows = array(
array('id' => 1, 'name' => 'John'),
array('id' => 2, 'name' => 'Mark')
);
$command = Yii::app()->db->createCommand();
$command->insertSeveral('users', $rows);
更新
正如Nabi K.A.Z.所提到的,最好不要触碰框架源代码。 在我的项目中,我实际上创建了一个名为MyCDbCommand的类(继承自CDbCommand),并创建了一个名为MyCDBConnection的类(继承自CDbConnection)。
MyCDbCommand(继承自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(扩展CDbConnection并使用MyCDbCommand):
class MyCDbConnection extends CDbConnection
{
public function createCommand($query = null)
{
$this->setActive(true);
return new MyCDbCommand($this, $query);
}
}
然后我修改了配置文件(/protected/config/main.php)。 我在那里将CDbConnection更改为MyCDbConnection:
...
'components'=>array(
...
'db' => array(
'connectionString' => 'mysql:host=localhost;dbname=dname',
'username' => 'user',
'password' => 'password',
'charset' => 'utf8',
'class' => 'MyCDbConnection', // Change default CDbConnection class to MyCDbConnection
),
...
)
...
现在您可以使用与之前相同的方式,而不必触及核心框架。
如果您不受限于使用Active Records,您可以使用DAO来执行适当的SQL插入语句以插入多行。您可以在此处找到这样一个语句的示例:
一旦您将插入语句作为字符串(如果您事先不知道需要插入多少行,则可以使用循环动态构建它),您可以像这样执行它:
$sql = 'INSERT statement goes here';
$connection = Yii::app() -> db;
$command = $connection -> createCommand($sql);
$command -> execute();
来自Pigalev Pavel的上述代码非常棒。
但是需要修改核心框架,这是不好的!
因此,我编写了一个独立的类。
将此代码放入components
文件夹下的GeneralRepository.php
文件中。
<?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);
}
}
并可在任何地方使用:
$rows = array(
array('id' => 1, 'name' => 'John'),
array('id' => 2, 'name' => 'Mark')
);
GeneralRepository::insertSeveral(User::model()->tableName(), $rows);