Search code examples
sqlyiidump

Execute SQL dump in Yii

I know I can execute (actually, creating it, not yet executed) custom SQL in Yii using:

$connection=Yii::app()->db;   // assuming you have configured a "db" connection
// If not, you may explicitly create a connection:
// $connection=new CDbConnection($dsn,$username,$password);
$command=$connection->createCommand($sql);
// if needed, the SQL statement may be updated as follows:
// $command->text=$newSQL;

Question: How can I execute a sql dump? i.e.

`$dump = file_get_contents('products.sql');`

Note: This dump has multiple commands - AFAIK createCommand works for single command - Am I wrong?

Note 2: I cannot break queries by ';' character (SQL delimiter) since such character is widely used among product data, so I have to execute the dump.

Solution

  • You CAN execute multiple commands with createCommand()

    MySQL:

    CREATE TABLE tst (
  f varchar(255) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

    PHP:

    ...
Yii::app()->db->createCommand("INSERT INTO tst SET f='test1'; INSERT INTO tst SET f='test2';")->execute();
...

    MySQL:

    mysql> select * from tst;
+-------+
| f     |
+-------+
| test1 |
| test2 |
+-------+
2 rows in set (0.00 sec)

    So if your products.sql contains valid SQL this should work:

    $sql = file_get_contents('products.sql');
Yii::app()->db->createCommand($sql)->execute();