PHP's YII framework using Database Configuration and SQL Walkthrough Tutorial _php instance

Database access (DAO)
YII contains a data access layer (DAO) built on top of the PHP PDO. DAO provides a unified set of APIs for different databases. Where ActiveRecord provides an interaction between the database and the model (M,model in MVC), QueryBuilder is used to create dynamic query statements. DAO provides a simple and efficient SQL query that you can use everywhere you interact with your database.

YII supports the following databases (DBMS) by default:

• Mysql
• Mariadb
• Sqlite
• PostgreSQL
• Cubrid: Version >= 9.3. (Because a bug reference value for the PHP PDO extension is invalid, you need to use 9.3 on the Cubrid client and server)
• Oracle
• MSSQL: Version >=2005.

Configuration

Starting with a database requires you to configure the database connectivity component, by adding the DB component to the application configuration implementation (the "underlying" Web application is config/web.php), and the DSN (data source name) is the database information, which is used to specify the databases.

 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=54
      32;dbname=mydatabase ',//PostgreSQL//' DSN ' => ' cubrid:dbname=demodb;host=localhost;port=33000 ',//Cubrid ' DSN ' => ' sqlsrv:server=localhost;database=mydatabase ',//MS SQL Server, sqlsrv driver//' DSN ' => ' dblib:h Ost=localhost;dbname=mydatabase ',//MS SQL Server, dblib driver//' DSN ' => ' Mssql:host=localhost;dbname=mydataba Se ',//MS SQL Server, MSSQL driver//' DSN ' => ' oci:dbname=//localhost:1521/mydatabase ',//Oracle ' Usernam
E ' => ' root ',//database username ' password ' => ',///Database Password ' charset ' => ' UTF8 ',],],///...]; 

Please refer to the PHP manual for more information about DSN format. After you configure a connection component, you can access it using the following syntax:

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

Please refer to yii\db\connection for a list of configurable properties. If you want to connect to the database through ODBC, you need to configure the Yii\db\connection::d rivername properties, such as:

' DB ' => [
  ' class ' => ' yii\db\connection ',
  ' drivername ' => ' mysql ',
  ' DSN ' => ' MySQL}; Server=localhost;database=test ',
  ' username ' => ' root ',
  ' password ' => ',
],

Note: If you need 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 ', 
    ],
  ],
  //...
];

Used in your code in the following ways:

$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 the use directly in your code:

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

Tip: If you need to perform additional SQL queries 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 executing SQL does not return any data, you can 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,delete method, which generates the appropriate SQL based on the parameters and executes it.

INSERT
$connection->createcommand ()->insert (' User ', [
  ' name ' => ' Sam ',
  ' age ' =>,
] )->execute ();

Insert multiple lines at once
$connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [[
  ' Tom ', 30],
  [' Jane ',]
  [' Linda ',],
]->execute ();

UPDATE
$connection->createcommand ()->update (' User ', [' Status ' => 1], ' Age > ')->execute ();

DELETE
$connection->createcommand ()->delete (' User ', ' status = 0 ')->execute ();

Referenced table and column names

The following syntax is used most of the time to safely refer to table and column names:

$sql = "Select COUNT ($column) from {{table}}}";
$rowCount = $connection->createcommand ($sql)->queryscalar ();

The above code $column to refer to the appropriate column name, while the {{table}} is converted to a reference to the appropriate table name. The table name has a special variable {{%Y}}, which can be automatically prefixed with the table name if the table prefix is set:

$sql = "Select COUNT ($column) from {% $table}}";
$rowCount = $connection->createcommand ($sql)->queryscalar ();

If you set the table prefix as follows in the configuration file, the above code will query the results 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 statements

You can use a preprocessing statement for a secure pass-through query parameter, which should first use: Placeholder 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 use 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 ();

Tip, it is more efficient to bind a variable before execution, and then change the value of the variable in each execution (typically in a loop).
Affairs

When you need to execute multiple related query sequences, you can encapsulate them in a single transaction to protect data consistency. YII provides a simple interface to implement transaction operations. Execute the SQL transaction query statement as follows:

$transaction = $connection->begintransaction ();
try {
  $connection->createcommand ($sql 1)->execute ();
   $connection->createcommand ($sql 2)->execute ();
  // ... Execute other SQL statements ...
  $transaction->commit ();
} catch (Exception $e) {
  $transaction->rollback ();
}

We start a transaction by Yii\db\connection::begintransaction () and catch the exception through a try catch. When execution succeeds, the transaction is committed through Yii\db\transaction::commit () and ends, The transaction is rolled back by Yii\db\transaction::rollback () when an exception failure occurs.

You can also nest multiple transactions if needed:

External transaction
$transaction 1 = $connection->begintransaction ();
try {
  $connection->createcommand ($sql 1)->execute ();

  Internal affairs
  $transaction 2 = $connection->begintransaction ();
  try {
    $connection->createcommand ($sql 2)->execute ();
    $transaction 2->commit ();
  } catch (Exception $e) {
    $transaction 2->rollback ();
  }

  $transaction 1->commit ();
} catch (Exception $e) {
  $transaction 1->rollback ();
}

Note that the database you are using must support savepoints to execute correctly, and the above code can be executed in all relational data, but only support savepoints can guarantee security.
YII also supports setting isolation levels for transactions isolation levels, which uses the database default isolation level when performing transactions, and you can also specify isolation levels for things. YII provides the following constants as a common isolation level

• \yii\db\transaction::read_uncommitted-allows read of changed uncommitted data that may result in dirty reads, non-repeatable reads, and Phantom reads
• \yii\db\transaction::read_committed-Allows read after concurrent transaction commits, and avoids dirty reads, which can result in repeated reads and Phantom reads.
• \yii\db\transaction::repeatable_read-consistency of multiple read results for the same field can result in phantom reads.
• \yii\db\transaction::serializable-Completely obeys the acid principle to ensure that no dirty reads, no repeatable reads, and phantom reads occur.

You can use the above constants or use a string string command to execute the command in the corresponding database to set the isolation level, for example, for postgres valid command for serializable READ only deferrable.

Note: Some databases can only set transaction isolation levels for connections, so you must explicitly define the isolation level for the connection. Database currently affected: MSSQL SQLite

Note: SQLite only supports two transaction isolation levels, so you can only set read UNCOMMITTED and SERIALIZABLE. Using other isolation levels throws an exception.

Note: PostgreSQL does not allow the isolation level to be set before the transaction starts, so you cannot specify the isolation level at the beginning of the transaction. You can set the Yii\db\transaction::setisolationlevel () after the transaction starts.
About isolation levels [isolation levels]: Http://en.wikipedia.org/wiki/Isolation_ (database_systems) #Isolation_levels

database replication and read/write separation

Many databases support database replication Http://en.wikipedia.org/wiki/Replication_ (computing) #Database_replication ">database Replication to increase availability and responsiveness. In database replication, data is always from the primary server to the server. All insert and update write operations are performed at the primary server, while read operations are performed from the server.

database replication and read/write separation can be achieved by configuring Yii\db\connection.

[
  ' class ' => ' yii\db\connection ',

  //Configure Master server
  ' DSN ' => ' DSN for master server ',
  ' username ' => ' Master ',
  ' password ' => ',

  //configuration from Server
  ' Slaveconfig ' => [
    ' username
    ' => ' slave ', ' Password ' => ',
    ' attributes ' => [
      //Use a smaller-connection timeout pdo::attr_timeout => 10,
   ],
  ],

  //configuration from the server group
  ' slaves ' => [
    [' DSN ' => ' DSN for slave server 1 '],
    [' DSN ' => ' DSN f or slave server 2 '],
    [' DSN ' => ' DSN for slave server 3 '],
    [' DSN ' => ' DSN for slave server 4 '],
  ],
   ]

The above configuration implemented a main multiple from the structure, from the server to perform read queries, the primary server to execute write query, read-write separation of functions by the background code automatically completed. The caller does not need to care. For example:

Use the above configuration to create a database connection object
$db = Yii::createobject ($config);

By performing a query operation from the server
$rows = $db->createcommand (' SELECT * from User LIMIT ')->queryall ();

The update operation is performed through
the primary server $db->createcommand ("Update user SET username= ' demo ' WHERE id=1")->execute ();

Note: Queries executed through Yii\db\command::execute () are considered write operations, and all other query methods performed using Yii\db\command are considered read. You can go through $db->. Slave gets the current use of the enabled from the server.
The connection component supports load balancing and failover from the server, and when the first read query is executed, a connection is selected from the server, and if the connection fails, the other is selected and the primary server is connected if all the servers are unavailable. You can configure Yii\db\connection::serverstatuscache to remember those that cannot be connected from the server and make yii in a period of time [[Yii\db\connection::serverretryinterval]. Does not repeatedly attempt to connect from the server that is not available at all.

Note: In the above configuration, each connection timeout from the server is specified as 10s. If the connection cannot be made within 10s, the server is considered to be dead. You can also customize the timeout parameter.
You can also configure a multiple-master multiple-from structure, such as:

[
  ' class ' => ' yii\db\connection ',

  //Configure Master server
  ' masterconfig ' => [
    ' username ' => ' master ',
    ' Password ' => ',
    ' attributes ' => [
      //Use a smaller-connection timeout pdo::attr_timeout => 10,
   ],

  //configuration from server group
  ' slaves ' => [
    [' DSN ' => ' DSN for slave server 1 '],
    [' DSN ' => ' DSN for slave s Erver 2 '],
    [DSN ' => ' DSN for slave server 3 '],
    [' DSN ' => ' DSN for slave server 4 '],
  ]

The above configuration developed 2 primary servers and 4 from servers. The connection component also supports load balancing and failover for the primary server, which, unlike from the server, throws an exception if all primary servers are unavailable.

Note: When you use Yii\db\connection::masters to configure one or more primary servers, other properties of the Connection about database connections (for example, Dsn,username, password) are ignored.
Transactions use the primary server's connection by default, and all operations in the execution of a transaction use the primary server connection, for example:

Start transaction on primary server connection
$transaction = $db->begintransaction ();

try {
  //All queries are executed on the primary server
  $rows = $db->createcommand (' SELECT * from User LIMIT ')->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 perform transaction operations from the server, you must specify them explicitly, for example:

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

Sometimes you want to force a master server to perform a read query, and you can call the Semaster () method.

$rows = $db->usemaster (function ($db) {return
  $db->createcommand (' SELECT * from User LIMIT ')->queryall ();
});

You can also set $db->enableslaves to false so that all queries are executed on the primary server.

• Manipulating Database schemas
• Get schema information

You can get schema information by Yii\db\schema an instance:

$schema = $connection->getschema ();

This example includes a series of methods to retrieve various information about the database:

$tables = $schema->gettablenames ();

For more information please refer to Yii\db\schema

Modify Mode

In addition to the underlying SQL query, Yii\db\command includes a series of methods to modify the database schema:

• Create/rename/delete/empty table
• Add/rename/delete/modify fields
• Add/Remove Primary key
• Add/Remove foreign keys
• Create/Delete Index

Use examples:

CREATE TABLE
$connection->createcommand ()->createtable (' Post ', [
  ' id ' => ' pk ',
  ' title ' => ') String ',
  ' text ' => ' text ',
]);

For a complete reference, see Yii\db\command.

SQL query Example:

Find the customers whose primary key value is $customers = Customer::findall (10);

$customer = Customer::findone (10);

The above code is equivalent to: $customers = Customer::find ()->where ([' ID ' =>])->all ();
Find the customers whose primary key value is ten, 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 ' => [A.,]]->all (); Find customers whose the whose status is 1 $customers = Customer::findall ([' Age ' =>, ' status ' => 1])

; 

The above code is equivalent to: $customers = Customer::find ()->where ([' Age ' =>, ' 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 ' =>, ' status ' => 1])->all ();

Get customers count $count = Customer::find ()->where ([' Age ' =>, ' status ' => 1])->count ();  add addition condition $customers = Customer::find ()->where ([' Age ' =>, ' status ' => 1])->andwhere (' Score

> ')->orderby (' id DESC ')->offset (5)->limit ()->all (); Find by SQL $customers = Customer::findbysql (' SELECT * from Customer WHERE age=30 and Status=1 and score>100 order B

 Y ID DESC LIMIT 5,10 ')->all ();

Modify:

Update status for customer-10
$customer = Customer::findone (a);
$customer->status = 1;
$customer->update ();

The above code is equivalent to:
customer::updateall ([' Status ' => 1], ' id =: id ', [': Id ' =>10]);

Delete:

Delete customer-10 Customer::findone (a)
->delete ();

The above code is equivalent to:
Customer::d eleteall ([' Status ' => 1], ' id =: id ', [': Id ' =>10]];

--------------------------------Use subqueries------------------------------------------

$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 ');

--------------------------------Handwriting SQL-------------------------------------------

Select
$customers = Yii:: $app->db->createcommand (' SELECT * from Customer ')->queryall ();

Update
Yii:: $app->db->createcommand ()->update (' Customer ', [' status ' =>1], ' id=10 ')->execute ( );

Delete
Yii:: $app->db->createcommand ()->delete (' Customer ', ' id=10 ')->execute ();

Transaction
//outer 
$transaction 1 = $connection->begintransaction ();
try {
  $connection->createcommand ($sql 1)->execute ();

  Internal
  $transaction 2 = $connection->begintransaction ();
  try {
    $connection->createcommand ($sql 2)->execute ();
    $transaction 2->commit ();
  } catch (Exception $e) {
    $transaction 2->rollback ();
  }

  $transaction 1->commit ();
} catch (Exception $e) {
  $transaction 1->rollback ();
}

-----------------------------Master and 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 '],]