PHP Development Framework Yii Framework Tutorial (24) Database-dao sample

The Access Object (DAO) provides 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).

$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.

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:

This example uses the MySQL Chinook database to modify the protected/config/main.php

' Components ' =>array ('     
    db ' =>array ('     
            class ' => ' cdbconnection ',     
            ' connectionString ' => ' MySQL : Host=localhost;dbname=chinook ',     
            ' 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.

For the sake of simplicity, we use the employee table in the Chinook database to modify the Datamodel

Class Datamodel     
{public     
    $employeeId;     
    public $firstName;     
    public $lastName;     
    public $title;     
    public $address;     
    public $email;     
}

Note: You can choose to create a datamodel this step.