Tutorial on using database configuration and SQL operations in PHP Yii framework

Database Access (DAO) Yii contains a data access layer (DAO) built on PHPPDO ). DAO provides a unified set of APIS for different databases. activeRecord provides interaction between databases and models (M, Model in MVC). QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries.

Database Access (DAO) Yii contains a data access layer (DAO) built on php pdo ). DAO provides a unified set of APIS for different databases. activeRecord provides interaction between databases and models (M, Model in MVC). QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries.

Database Access (DAO)
Yii contains a data access layer (DAO) built on php pdo ). DAO provides a unified set of APIS for different databases. activeRecord provides interaction between databases and models (M, Model in MVC). QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries, which can be used in various areas of interaction with databases.

By default, Yii supports the following databases (DBMS ):

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: version> = 9.3. (because a bug reference value of the php pdo extension will be invalid, you need to use 9.3 on both the client and server of CUBRID)
• Oracle
• MSSQL: version> = 2005.

Configuration

To start using a database, you must first configure the database connection component and add the db component to the application configuration. (the "Basic" Web application is config/web. php), DSN (Data Source Name) is the Name of the Data Source, used to specify the database information. as follows:

Return [//... 'components' => [//... 'db' => ['class' => 'yii \ db \ connection', 'dsn '=> 'mysql: host = localhost; dbname = mydatabase', // mysql, mariaDB // 'dsn '=> 'sqlite:/path/to/database/file', // sqlite // 'dsn' => 'pgsql: host = localhost; port = 5432; dbname = mydatabase', // PostgreSQL // 'dsn '=> 'cubrid: dbname = demodb; host = localhost; port = 100 ', // CUBRID // 'dsn '=> 'sqlsrv: Server = localhost; Database = mydatabase', // ms SQL Server, sqlsrv driver // 'dsn' => 'dblib: host = localhost; dbname = mydatabase', // ms SQL Server, dblib driver // 'dsn '=> 'mssql: host = localhost; dbname = mydatabase ', // ms SQL Server, mssql driver // 'dsn '=> 'oss: dbname = // localhost: 1521/mydatabase ', // Oracle 'username' => 'root', // database username 'Password' => '', // Database Password 'charset' => 'utf8',],], //...];

For more information about the DSN format, see PHP manual. After configuring the connection component, you can use the following syntax to access it:

$connection = \Yii::$app->db;

See yii \ db \ Connection to obtain the configurable attribute list. If you want to connect to the database through ODBC, You need to configure the yii \ db \ Connection: driverName attribute, for example:

'db' => [  'class' => 'yii\db\Connection',  'driverName' => 'mysql',  'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',  'username' => 'root',  'password' => '',],

Note: To use multiple databases at the same time, you can define multiple connection components:

return [  // ...  'components' => [    // ...    'db' => [      'class' => 'yii\db\Connection',      'dsn' => 'mysql:host=localhost;dbname=mydatabase',       'username' => 'root',      'password' => '',      'charset' => 'utf8',    ],    'secondDb' => [      'class' => 'yii\db\Connection',      'dsn' => 'sqlite:/path/to/database/file',     ],  ],  // ...];

Use the following method in the Code:

$primaryConnection = \Yii::$app->db;$secondaryConnection = \Yii::$app->secondDb;

If you do not want to define a database connection as a global application component, You can initialize it in the Code:

$connection = new \yii\db\Connection([  'dsn' => $dsn,   'username' => $username,   'password' => $password,]);$connection->open();

TIPS: If you need to execute an additional SQL query after creating a connection, you can add the following code to the application configuration file:

return [  // ...  'components' => [    // ...    'db' => [      'class' => 'yii\db\Connection',      // ...      'on afterOpen' => function($event) {        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();      }    ],  ],  // ...];

If the SQL statement does not return any data, use the execute method in the command:

$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');$command->execute();

You can use the insert, update, and delete methods to generate appropriate SQL statements based on the parameters and execute them.

// INSERT $ connection-> createCommand ()-> insert ('user', ['name' => 'Sam ', 'age' => 30,]) -> execute (); // INSERT multiple rows at a time $ connection-> createCommand ()-> batchInsert ('user', ['name', 'age'], [['Tom ', 30], ['Jane', 20], ['linda ', 25],])-> execute (); // UPDATE $ connection-> createCommand ()-> update ('user', ['status' => 1], 'Age> 30')-> execute (); // DELETE $ connection-> createCommand ()-> delete ('user', 'status = 0')-> execute ();

Referenced table name and column name

Most of the time, the following syntax is used to safely reference the table name and column Name:

$sql = "SELECT COUNT($column) FROM {{table}}";$rowCount = $connection->createCommand($sql)->queryScalar();

The Code $ column above is changed to reference an appropriate column name, and {table} is changed to reference an appropriate table name. The table name has a special variable {% Y}. If you set the table prefix, you can use this variant to automatically add the prefix before the table name:

$sql = "SELECT COUNT($column) FROM {{%$table}}";$rowCount = $connection->createCommand($sql)->queryScalar();

If the table prefix is set as follows in the configuration file, the above Code will query the result in the tbl_table table:

return [  // ...  'components' => [    // ...    'db' => [      // ...      'tablePrefix' => 'tbl_',    ],  ],];

Another option for manually referencing table names and column names is to use yii \ db \ Connection: quoteTableName () and yii \ db \ Connection: quoteColumnName ():

$column = $connection->quoteColumnName($column);$table = $connection->quoteTableName($table);$sql = "SELECT COUNT($column) FROM $table";$rowCount = $connection->createCommand($sql)->queryScalar();

Preprocessing statement

To pass query parameters safely, you can use a preprocessing statement. You should first use the placeholder and then bind the variable to the corresponding placeholder:

$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');$command->bindValue(':id', $_GET['id']);$post = $command->query();

Another method is to prepare a preprocessing statement and execute multiple queries:

$command = $connection->createCommand('DELETE FROM post WHERE id=:id');$command->bindParam(':id', $id);$id = 1;$command->execute();$id = 2;$command->execute();

Note: it is more efficient to bind a variable before execution, and then change the value of the variable in each execution (usually used in a loop.
Transactions

When you need to execute multiple related queries sequentially, You can encapsulate them into a transaction to protect data consistency. yii provides a simple interface for transaction operations. execute the SQL transaction query statement as follows:

$ Transaction = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql1)-> execute (); $ connection-> createCommand ($ sql2) -> execute ();//... execute other SQL statements... $ transaction-> commit ();} catch (Exception $ e) {$ transaction-> rollBack ();}

We start a transaction through yii \ db \ Connection: beginTransaction () and catch exceptions through try catch. when the execution is successful, the Transaction is committed and ended through yii \ db \ Transaction: commit (). If an exception fails, the Transaction is rolled back through yii \ db \ Transaction: rollBack.

You can also nest multiple transactions as needed:

// External transaction $ transaction1 = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql1)-> execute (); // internal transaction $ transaction2 = $ connection-> beginTransaction (); try {$ connection-> createCommand ($ sql2)-> execute (); $ transaction2-> commit ();} catch (Exception $ e) {$ transaction2-> rollBack ();} $ transaction1-> commit ();} catch (Exception $ e) {$ transaction1-> rollBack ();}

Note that the database you are using must support Savepoints for proper execution. The above code can be executed in all relational data, but only Savepoints can ensure security.
Yii also supports setting the isolation level isolation levels for the transaction. When executing the transaction, the default isolation level of the database will be used. You can also specify the isolation level for the transaction. yii provides the following constants as common isolation levels

• \ Yii \ db \ Transaction: READ_UNCOMMITTED-allows reading changed uncommitted data, which may lead to dirty reads, non-repeated reads, and Phantom reads.
• \ Yii \ db \ Transaction: READ_COMMITTED-allows concurrent transactions to be read after being committed to avoid dirty reads, which may lead to repeated reads and Phantom reads.
• \ Yii \ db \ Transaction: REPEATABLE_READ-the read results of the same field are consistent multiple times, which can lead to phantom read.
• \ Yii \ db \ Transaction: SERIALIZABLE-fully complies with ACID principles to ensure that dirty reads, repeated reads, and Phantom reads are not performed.

You can use the above constants or a string command to execute this command in the corresponding database to set the isolation level. For example, the effective command for ipvs is serializable read only deferrable.

Note: Some databases can only set transaction isolation levels for connections, so you must specify the isolation level for the connections. Currently, the affected database: MSSQL SQLite

Note: SQLite only supports two transaction isolation levels. Therefore, you can only set read uncommitted and SERIALIZABLE. Exceptions are thrown when other isolation levels are used.

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts. Therefore, you cannot specify the isolation level when the transaction starts. you can call yii \ db \ Transaction: setIsolationLevel () after the Transaction starts to set.
About isolation level [isolation levels]: http://en.wikipedia.org/wiki/Isolation_ (database_systems) # Isolation_levels

Database Replication and read/write splitting

Many databases support database replication http://en.wikipedia.org/wiki/Replication_ (computing) # Database_replication "> database replication to improve availability and response speed. in database replication, data is always from the master server to the slave server. all insert and update write operations are performed on the master server, while read operations are performed on the slave server.

You can configure yii \ db \ Connection to implement database replication and read/write splitting.

['Class' => 'yii \ db \ connection', // configure the master server 'dsn '=> 'dsn for master Server', 'username' => 'master ', 'Password' => '', // configure the slave server 'slveconfig' => ['username' => 'slave ', 'Password' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the slave server group 'slafs' => [['dsn '=> 'dsn for slave server 1'], ['dsn '=> 'dsn for slave server 2'], ['dsn' => 'dsn for slave server 3'], ['dsn '=> 'dsn for slave server 4'],],]

The preceding configuration implements a master-slave structure. The slave server executes read queries, and the master server executes write queries. The read/write splitting function is automatically completed by the background code. the caller does not need to care about it. for example:

// Use the preceding configuration to create a database connection object $ db = Yii: createObject ($ config ); // query FROM the server $ rows = $ db-> createCommand ('select * FROM user LIMIT 10')-> queryAll (); // perform the UPDATE operation on the master server $ db-> createCommand ("UPDATE user SET username = 'Demo' WHERE id = 1")-> execute ();

Note: The query executed through yii \ db \ Command: execute () is considered as a write operation, all other query methods that use yii \ db \ Command are considered as read operations. you can use $ db-> slave to obtain the slave server currently in use.
The Connection component supports Server Load balancer and failover. When you perform a read query for the first time, a slave server is selected for Connection. If the Connection fails, another slave server is selected, if all slave servers are unavailable, the master server is connected. You can configure yii \ db \ Connection: serverStatusCache to remember slave servers that cannot be connected, so that Yii [[yii \ db \ Connection: serverRetryInterval] in a period of time. will not try to connect to those slave servers that are basically unavailable.

Note: In the preceding configuration, each slave server connection timeout value is set to 10 s. if the connection fails within 10 seconds, the server is considered to have crashed. you can also customize the timeout parameter.
You can also configure a multi-master, multi-slave structure, for example:

['Class' => 'yii \ db \ connection', // configure the master server 'masterconfig' => ['username' => 'master ', 'Password' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the master server group 'masters '=> [['dsn' => 'dsn for master server 1'], ['dsn '=> 'dsn for master server 2'],], // configure the slave server 'slaveconfig' => ['username' => 'slave ', 'Password' => '', 'bubuckets' => [// use a smaller connection timeout PDO: ATTR_TIMEOUT => 10,],], // configure the slave server group 'slafs' => [['dsn '=> 'dsn for slave server 1'], ['dsn '=> 'dsn for slave server 2'], ['dsn' => 'dsn for slave server 3'], ['dsn '=> 'dsn for slave server 4'],],]

The preceding configuration sets two master servers and four slave servers. the Connection component also supports load balancing and Failover of the master server. Unlike the slave server, if all the master servers are unavailable, an exception is thrown.

Note: When you use yii \ db \ Connection: masters to configure one or more Master servers, other database Connection attributes in Connection (such as dsn, username, password) will be ignored.
Transactions use the connection of the master server by default, and all operations performed by the transaction use the connection of the master server. For example:

// Start the transaction on the master server connection $ transaction = $ db-> beginTransaction (); try {// All queries are executed on the master server $ rows = $ db-> createCommand ('select * FROM user LIMIT 10')-> queryAll (); $ db-> createCommand ("UPDATE user SET username = 'Demo' WHERE id = 1")-> execute (); $ transaction-> commit ();} catch (\ Exception $ e) {$ transaction-> rollBack (); throw $ e ;}

If you want to execute transaction operations on the slave server, you must specify them explicitly, for example:

$transaction = $db->slave->beginTransaction();

Sometimes you want to force the master server to execute read queries, you can call the seMaster () method.

$rows = $db->useMaster(function ($db) {  return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();});

You can also set $ db-> enableSlaves to false to execute all queries on the master server.

• Database Operation Mode
• Obtain mode information

You can obtain Schema information through the yii \ db \ Schema instance:

$schema = $connection->getSchema();

This instance includes a series of methods to retrieve information from multiple aspects of the database:

$tables = $schema->getTableNames();

For more information, see yii \ db \ Schema

Modify Mode

In addition to basic SQL queries, yii \ db \ Command also includes a series of methods to modify the database mode:

• Create/rename/delete/clear a table
• Add/rename/delete/modify Fields
• Add/delete primary keys
• Add/delete Foreign keys
• Create/delete an index

Example:

// Create a table $ connection-> createCommand ()-> createTable ('post', ['id' => 'pk', 'title' => 'string ', 'text' => 'text',]);

For more information, see yii \ db \ Command.

SQL query example:

// find the customers whose primary key value is 10$customers = Customer::findAll(10);$customer = Customer::findOne(10);// the above code is equivalent to:$customers = Customer::find()->where(['id' => 10])->all();// find the customers whose primary key value is 10, 11 or 12.$customers = Customer::findAll([10, 11, 12]);$customers = Customer::find()->where(['IN','id',[10,11,12]])->all();// the above code is equivalent to:$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();// find customers whose age is 30 and whose status is 1$customers = Customer::findAll(['age' => 30, 'status' => 1]);// the above code is equivalent to:$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();// use params binding$customers = Customer::find()->where('age=:age AND status=:status')->addParams([':age'=>30,':status'=>1])->all();// use index$customers = Customer::find()->indexBy('id')->where(['age' => 30, 'status' => 1])->all();// get customers count$count = Customer::find()->where(['age' => 30, 'status' => 1])->count();// add addition condition$customers = Customer::find()->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();// find by sql$customers = Customer::findBySql('SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();

Modify:

// update status for customer-10$customer = Customer::findOne(10);$customer->status = 1;$customer->update();// the above code is equivalent to:Customer::updateAll(['status' => 1], 'id = :id',[':id'=>10]);

Delete:

// delete customer-10Customer::findOne(10)->delete();// the above code is equivalent to:Customer::deleteAll(['status' => 1], 'id = :id',[':id'=>10]);

------------------------------ Use subquery ------------------------------------------

$subQuery = (new Query())->select('COUNT(*)')->from('customer');// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`$query = (new Query())->select(['id', 'count' => $subQuery])->from('customer');

-------------------------------- Handwritten SQL -------------------------------------------

// select$customers = Yii::$app->db->createCommand('SELECT * FROM customer')->queryAll();// updateYii::$app->db->createCommand()->update('customer',['status'=>1],'id=10')->execute();// deleteYii::$app->db->createCommand()->delete('customer','id=10')->execute();//transaction// outer $transaction1 = $connection->beginTransaction();try {  $connection->createCommand($sql1)->execute();  // internal  $transaction2 = $connection->beginTransaction();  try {    $connection->createCommand($sql2)->execute();    $transaction2->commit();  } catch (Exception $e) {    $transaction2->rollBack();  }  $transaction1->commit();} catch (Exception $e) {  $transaction1->rollBack();}

----------------------------- Master/Slave configuration --------------------------------------------

[  'class' => 'yii\db\Connection',  // master   'dsn' => 'dsn for master server',  'username' => 'master',  'password' => '',  // slaves  'slaveConfig' => [    'username' => 'slave',    'password' => '',    'attributes' => [      // use a smaller connection timeout      PDO::ATTR_TIMEOUT => 10,    ],  ],  'slaves' => [    ['dsn' => 'dsn for slave server 1'],    ['dsn' => 'dsn for slave server 2'],    ['dsn' => 'dsn for slave server 3'],    ['dsn' => 'dsn for slave server 4'],  ],]