A summary of Yii learning data Access Object (DAO) _php instance

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 DAO can easily switch between using different database management systems without having to modify the data access code.

Data Access Objects (DAO) provide a common API for accessing data stored in different database management systems (DBMS). Therefore, when you replace the underlying DBMS with another, you do not need to modify the code that uses DAO to access the data.

The Yii DAO is built based 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 the Yii dao,pdo extension and a specific PDO database driver (for example, Pdo_mysql) must be installed.

The Yii DAO mainly includes the following four classes:

Cdbconnection: Represents a database connection.
Cdbcommand: Represents a SQL statement executed through a database.
Cdbdatareader: Represents a stream that moves forward only, from a row in a query result set.
Cdbtransaction: Represents a database transaction.
Below, we introduce the application of Yii DAO in different scenarios.

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

Copy Code code as follows:

$connection =new cdbconnection ($dsn, $username, $password);
Establish a connection. You can use Try...catch to catch exceptions that might be thrown
$connection->active=true;
......
$connection->active=false; Close connection

The format of the DSN depends on the PDO database driver you are using. 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 common DSN formats.

Copy Code code as follows:

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

Because 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:

Copy code code 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 via Yii::app ()->db. It has been activated automatically unless we deliberately configure Cdbconnection::autoconnect to false. In this way, this single DB connection can be shared in many parts of our code.

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

Copy Code code as follows:

$connection =yii::app ()->db; Let's say you've built 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 as follows:
$command->text= $newSQL;

An SQL statement can be executed by Cdbcommand in two ways as follows:

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 a SQL statement that returns several rows of data, such as a SELECT. If successful, it returns a Cdbdatareader instance through which the result rows 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.

Copy Code code as follows:

$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 result by repeatedly calling Cdbdatareader::read (). You can also use Cdbdatareader to retrieve data in a single line of PHP's foreach language structure.

Copy Code code as follows:

$dataReader = $command->query ();
Repeat call read () until it returns false
while ($row = $dataReader->read ())!==false) {...}
Use foreach to traverse each row in the data
foreach ($dataReader as $row) {...}
Extract 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 that represents the first row of the query result.

4. Use of transactions
When an application executes several queries, each query reads from the database and/or writes the information to the database, it is important to ensure that the database does not leave a few queries and executes only a few other queries. transactions, which are represented as cdbtransaction instances in Yii, may be started in the following scenarios:

Start a transaction.
Execute the query. Any updates to the database are not visible to the outside world.
Commit the transaction. If the transaction succeeds, the update becomes visible.
If one of the queries fails, the entire transaction rolls back.
The above workflows can be implemented by using the following code:

Copy Code code as follows:

$transaction = $connection->begintransaction ();
Try
{
$connection->createcommand ($sql 1)->execute ();
$connection->createcommand ($sql 2)->execute ();
.... Other SQL executions
$transaction->commit ();
}
catch (Exception $e)//If one of the queries fails, an exception is thrown
{
$transaction->rollback ();
}

5. Binding parameters
To avoid SQL injection attacks and improve the efficiency of recurring SQL statements, you can "prepare (prepare)" An SQL statement with optional parameter placeholders that are replaced with actual arguments when the parameter is bound.

A parameter placeholder can be named (represented as a unique tag) or unnamed (represented as a question mark). Call Cdbcommand::bindparam () or Cdbcommand::bindvalue () to replace these placeholders with actual parameters. These parameters do not need to be enclosed in quotes: the underlying database driver will handle this for you. The parameter bindings must be completed before the SQL statement executes.

Copy Code code as follows:

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

Method Bindparam () and Bindvalue () are very similar. The only difference is that the former uses a PHP variable to bind the parameter, and the latter uses a value. For those large chunks of data in memory, in the performance considerations, priority should be given to using the former.

For more information on binding parameters, refer to the relevant PHP documentation.

6. Bound columns
When you get the query results, you can also use PHP variables to bind columns. This will automatically populate with the most recent values each time you get a row in the query's results.

Copy Code code as follows:

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

7. Use Table Prefix
From version 1.1.0, YII provides integrated support for using table prefixes. A table prefix is a string that is added before the name of a datasheet in the currently connected database. It is commonly used in a shared server environment in which multiple applications may share the same database, using different table prefixes to differentiate each other. For example, an application can use Tbl_ as a table prefix and another can use Yii_.

To use the table prefix, configure the Cdbconnection::tableprefix property to the desired table prefix. You then use {{tablename}}} 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:

Copy Code code as follows:

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