Yii Framework database Operation data Access object (DAO) Simple summary

YII provides powerful database programming support. The Yii data Access Object (DAO) is built on the PHP data object (PDO) extension, making it possible to access different database management systems (DBMS) in a single unified interface. Applications developed using Yii's DAO can easily switch between using different database management systems without needing to modify the data access code. Yii's Active Record (AR) implements a widely used object-relational mapping (ORM) approach to further simplifying database programming. By convention, a class represents a table and an instance represents a row of data. Yii AR eliminates the repetitive tasks of most SQL statements that are used to handle CRUD (create, read, update, and delete) data operations.
Although Yii's DAO and AR are capable of handling almost all database related tasks, you can still use your own database in Yii application. In fact, the YII framework is designed so that it can be used concurrently with other third-party libraries.
Data Access Objects (DAO)

Yii DAO is built on PHP Data Objects (PDO). It is an extension of unified data access for many popular DBMS including MySQL, PostgreSQL, and so on. Therefore, to use YII dao,pdo extensions and specific PDO database drivers (such as pdo_mysql) must be installed.
Yii DAO mainly consists of the following four classes:
Cdbconnection: Represents a database connection.
Cdbcommand: Represents a SQL statement executed through the database.
Cdbdatareader: Represents a stream that moves forward only, from a row in a query result set.
Cdbtransaction: Represents a database transaction.
1. Establish database connection

To establish a database connection, create a cdbconnection instance and activate it. Connecting to a database requires a data source name (DSN) to specify the connection information. The user name and password may also be used. An exception is thrown when an error occurs during connection to the database (for example, an incorrect DSN or an invalid user name/password).

HTML "data-pbcklang=" HTML "data-pbcktabsize=" 4 ">
$connection =new cdbconnection ($dsn, $username, $password); Establish the connection. You can use Try...catch to catch exceptions that can be thrown

$connection->active=true;

......

$connection->active=false; Close connection

The format of the DSN depends on the PDO database driver used. In general, the DSN should contain the PDO-driven name, followed by a colon, followed by the driver-specific connection syntax. You can consult the PDO documentation for more information. The following is a list of commonly used DSN formats.

* Sqlite:sqlite:/path/to/dbfile
* MYSQL:MYSQL:HOST=LOCALHOST;DBNAME=TESTDB
* POSTGRESQL:PGSQL:HOST=LOCALHOST;PORT=5432;DBNAME=TESTDB
* SQL Server:mssql:host=localhost;dbname=testdb
* ORACLE:OCI:DBNAME=//LOCALHOST:1521/TESTDB

Since Cdbconnection inherits from Capplicationcomponent, we can also use it as an application component. To do this, configure a db (or other Name) application component in the application configuration as follows:

Array

...... ' Components ' =>array (

...... ' DB ' =>array (

' Class ' = ' cdbconnection ',

' connectionString ' = ' mysql:host=localhost;dbname=testdb ',

' Username ' = ' root ',

' Password ' = ' password ',

' Emulateprepare ' =>true,//needed by some MySQL installations

),

),

)

Then we can access the database connection through Yii::app ()->db. It has been automatically activated, unless we intentionally configured Cdbconnection::autoconnect to False. In this way, this single DB connection can be shared in many places in our code.
2. Execute SQL statements

Once the database connection is established, the SQL statement can be executed by using Cdbcommand. You can create an Cdbcommand instance by calling Cdbconnection::createcommand () as a parameter by using the specified SQL statement.

$connection =yii::app ()->db; Suppose you have established a "DB" connection//if not, you may need to explicitly establish a connection:

$connection =new cdbconnection ($dsn, $username, $password);

$command = $connection->createcommand ($sql); If necessary, this SQL statement can be modified in the following ways:

$command->text= $newSQL;

An SQL statement is executed in the following two ways through Cdbcommand:
Execute (): Executes a query-free (non-query) SQL statement, such as INSERT, UPDATE, and DELETE. If successful, it returns the number of rows affected by this execution.
Query (): Executes an SQL statement that returns several rows of data, such as SELECT. If successful, it returns a Cdbdatareader instance through which the result row of the data can be traversed. For simplicity, (YII) also implements a series of queryxxx () methods to directly return query results.
An exception will be thrown if an error occurs while executing the SQL statement.

$rowCount = $command->execute (); Execute Query-free SQL

$dataReader = $command->query (); Execute an SQL query

$rows = $command->queryall (); Query and return all rows in the result

$row = $command->queryrow (); Query and return the first row in the result

$column = $command->querycolumn (); Query and return the first column in the result

$value = $command->queryscalar (); Query and return the first field in the first row of the result

3. Get query Results

After Cdbcommand::query () generates the Cdbdatareader instance, you can get the rows in the results by repeating the call to Cdbdatareader::read (). You can also retrieve data in the Foreach language structure of PHP using a cdbdatareader row of rows.

$dataReader = $command->query (); Repeats the call to read () until it returns false

while (($row = $dataReader->read ())!==false) {...}//use foreach to iterate through each row in the data

foreach ($dataReader as $row) {...}//Fetch all rows to an array at once

$rows = $dataReader->readall ();

Note: Unlike query (), all queryxxx () methods return data directly. For example, Queryrow () returns an array representing the first row of the query results.
4. Use transactions

transactions, which are represented as cdbtransaction instances in Yii, may be started in the following scenarios:
* Start a transaction.
* Execute each query. Any updates to the database are not visible to the outside world.
* Commit a transaction. If the transaction succeeds, the update becomes visible.
* If one of the queries fails, the entire transaction is rolled back.
The above workflow can be implemented with the following code:

$transaction = $connection->begintransaction ();

try {
$connection->createcommand ($sql 1)->execute ();
$connection->createcommand ($sql 2)->execute (); .... Other SQL executions
$transaction->commit ();

} catch (Exception $e)//If a query fails, an exception is thrown

{
$transaction->rollback ();
}

5. Binding parameters

To avoid SQL injection attacks and increase the efficiency of repeated SQL statements, you can "prepare" (prepare) a SQL statement with optional parameter placeholders, which will be replaced with actual parameters at parameter binding.
The parameter placeholder can be either named (represented as a unique token) or unnamed (represented as a question mark). Call Cdbcommand::bindparam () or Cdbcommand::bindvalue () to replace these placeholders with the actual parameters. These parameters do not need to be quoted: the underlying database driver will take care of this for you. Parameter bindings must be completed before the SQL statement executes.

A SQL with two placeholders ": Username" and ": E-mail"
$sql = "INSERT into Tbl_user (username, email) VALUES (: Username,:email)";
$command = $connection->createcommand ($sql);
Replace the placeholder ": username" with the actual user name
$command->bindparam (": Username", $username, PDO::P aram_str);
Replace the placeholder with the actual email ": E-mail"
$command->bindparam (": Email", $email, PDO::P aram_str);
$command->execute ();
Insert another row with a new parameter set
$command->bindparam (": username", $username 2,pdo::P aram_str);
$command->bindparam (": Email", $email 2,pdo::P aram_str);
$command->execute ();

Methods Bindparam () and Bindvalue () are very similar. The only difference is that the former uses a PHP variable binding parameter, and the latter uses a value. For those large data block parameters in memory, in the performance considerations, the former should be used preferentially.
6. Binding columns

When you get the results of a query, you can also bind columns using PHP variables. This will automatically populate with the most recent values each time you get a row in the query results.

$sql = "Select username, email from tbl_user";
$dataReader = $connection->createcommand ($sql)->query ();
Binding the first column with a $username variable (username)
$dataReader->bindcolumn (1, $username);
Bind second column with $email variable (email)
$dataReader->bindcolumn (2, $email);
while ($dataReader->read ()!==false)
{
$username and $email contain username and email in the current line
}

7. Using Table prefixes

To use the table prefix, configure the Cdbconnection::tableprefix property to the desired table prefix. The {{TableName}} is then used in the SQL statement to represent the name of the table, where TableName refers to the table name without a prefix. For example, if the database contains a table named Tbl_user, and Tbl_ is configured as a table prefix, then we can execute user-related queries using the following code:

$sql = ' SELECT * from {{user}} ';
$users = $connection->createcommand ($sql)->queryall ();

Original link: http://www.kubiji.cn/topic-id1173.html

Yii Framework database Operation data Access object (DAO) Simple summary