PHP development framework YiiFramework tutorial (24) database-DAO example

PHP development framework YiiFramework tutorial (24) database-DAO example

The Data Access Object (DAO) provides a common API for accessing data stored in different database management systems (DBMS. Therefore, when changing the underlying DBMS to another one, you do not need to modify the code that uses DAO to access data.

Yii DAO is built based on PHP Data Objects (PDO. It is an extension that provides unified data access for many popular DBMS, including MySQL and PostgreSQL. Therefore, to use Yii DAO, PDO extensions, and specific PDO database drivers (such as PDO_MYSQL), you must install them.

Yii DAO mainly includes the following four classes:

CDbConnection: represents a database connection.

CDbCommand: indicates an SQL statement executed through the database.

CDbDataReader: a stream that only moves forward from a row in a query result set.

CDbTransaction: represents a database transaction.

Next, we will introduce the application of Yii DAO in different scenarios. 1. establish a database connection

To establish a database connection, create a CDbConnection instance and activate it. To connect to the database, a data source name (DSN) is required to specify the connection information. The user name and password may also be used. When an error occurs during Database Connection (for example, an incorrect DSN or invalid username/password), an exception is thrown.

$ Connection = new CDbConnection ($ dsn, $ username, $ password );
// Establish a connection. You can use try... catch to catch exceptions that may be thrown.
$ Connection-> active = true;
......
$ Connection-> active = false; // The format of closing the connection DSN depends on the PDO database driver used. In general, DSN must contain the name of the PDO driver, followed by a colon, and followed by the driver-specific connection syntax. For more information, see The PDO documentation. 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

Because CDbConnection inherits from CApplicationComponent, it can also be used as an application component. To do this, configure a db (or other name) application component in the application configuration as follows:

In this example, use the MySQL chinook database to modify protected/config/main. php.

'Components' => array (
'DB' => array (
'Class' => 'cdbconnection ',
'Connectionstring' => 'MySQL: host = localhost; dbname = chinook ',
'Username' => 'root ',
'Password' => 'password ',
'Default' => true, // needed by some MySQL installations
),
),

Then we can access the database connection through Yii: app ()-> db. It has been automatically activated, unless we have specially configured CDbConnection: autoConnect to false. In this way, this separate DB connection can be shared in many places in our code.

2. execute SQL statements

After the database connection is established, the SQL statement can be executed by using CDbCommand. You can call CDbConnection: createCommand () using the specified SQL statement as the parameter to create a CDbCommand instance.

For simplicity, we use the Employee table in the Chinook database to modify DataModel

Class DataModel
{
Public $ employeeId;
Public $ firstName;
Public $ lastName;
Public $ title;
Public $ address;
Public $ email;
}

Note: You can select this step to create DataModel.

Modify the indexAction method of SiteController:

Public function

ActionIndex ()
{

$ Model = array ();
$ SQL = 'select * FROM Employee ';
// Assume that you have established a "db" connection
$ Connection = Yii: app ()-> db;
// If not, you may need to explicitly establish a connection:
// $ Connection = new CDbConnection ($ dsn, $ username, $ password );
$ Command = $ connection-> createCommand ($ SQL );
// If necessary, the SQL statement can be modified as follows:
// $ Command-> text = $ newSQL;

$ DataReader = $ command-> query ();

// Each $ row is an array representing a row of data
Foreach ($ dataReader as $ row)
{
$ Employee = new DataModel ();
$ Employee-> employeeId = $ row ['employeeid'];
$ Employee-> firstName = $ row ['firstname'];
$ Employee-> lastName = $ row ['lastname'];
$ Employee-> title = $ row ['title'];
$ Employee-> address = $ row ['address'];
$ Employee-> email = $ row ['email '];

$ Model [] = $ employee;
}

$ This-> render ('index', array (
'Model' => $ model,

));
}

An SQL statement is executed in the following two methods:

Execute (): execute a non-query SQL statement, such as INSERT, UPDATE, and DELETE. If the execution succeeds, it returns the number of rows affected by the execution. Query (): execute an SQL statement that returns several rows of data, for example, SELECT. If it succeeds, it returns a CDbDataReader instance through which the result row of data can be traversed. For convenience, (Yii) also implements a series of queryXXX () methods to directly return the query results.

If an error occurs during SQL statement execution, an exception is thrown.

$ RowCount = $ command-> execute (); // run the SQL statement without query
$ 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 (); // The first column in the query result
$ Value = $ command-> queryScalar (); // query and return the first field in the first row of the result. 3. obtain the query result.

After CDbCommand: query () generates a CDbDataReader instance, you can call CDbDataReader: read () to obtain the rows in the result. You can also use CDbDataReader to retrieve data in a row in the foreach structure of PHP.

$ DataReader = $ command-> query ();
// Call read () repeatedly until it returns false
While ($ row = $ dataReader-> read ())! = False ){...}
// Use foreach to traverse each row of data
Foreach ($ dataReader as $ row ){...}
// Extract all rows to an array at a time
$ Rows = $ dataReader-> readAll (); 4. display query results

For the sake of simplicity, this example uses the echo statement to display the Employee records. you can use the GridView or ListView statement to display the database tables.

Modify protected/views/site/index. php

Foreach ($ model as $ employee)
{

Echo 'employeeid: '. $ employee-> EmployeeId .'
';
Echo 'first Name: '. $ employee-> firstName .'
';
Echo 'last Name: '. $ employee-> lastName .'
';
Echo 'Title: '. $ employee-> Title .'
';
Echo 'address: '. $ employee-> Address .'
';
Echo 'email: '. $ employee-> Email .'
';
Echo '----------------------
';
}

?>

5. use transactions

When an application executes several queries and each query reads information from the database and/or writes information to the database, it is very important to ensure that the database does not leave several queries but only executes several other queries. Transaction, represented as a CDbTransaction instance in Yii, may be started in the following situations:

Start transactions. execute queries one by one. Any updates to the database are invisible to the outside world. Commit a transaction. If the transaction is successful, the update becomes visible. If one of the queries fails, the entire transaction is rolled back.

The preceding workflow can be implemented using the following code:

$ Transaction = $ connection-> beginTransaction ();
Try
{
$ Connection-> createCommand ($ sql1)-> execute ();
$ Connection-> createCommand ($ sql2)-> execute ();
//... Other SQL executions
$ Transaction-> commit ();
}
Catch (Exception $ e) // if a query fails, an Exception is thrown.
{
$ Transaction-> rollBack ();
}

6. bind parameters

To avoid SQL injection attacks and improve the efficiency of repeatedly executing SQL statements, you can "prepare (prepare)" an SQL statement containing the placeholder optional parameters. when binding parameters, these placeholders are replaced with actual parameters.

Parameter placeholders 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 by quotation marks: the underlying database driver will handle this for you. Parameter binding must be completed before the SQL statement is executed.

// An SQL statement with two placeholders ": username" and ": email"
$ 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: PARAM_STR );
// Replace the placeholder ": Email" with the actual email address"
$ Command-> bindParam (": email", $ email, PDO: PARAM_STR );
$ Command-> execute ();
// Insert another row using the new parameter set
$ Command-> bindParam (": username", $ username2, PDO: PARAM_STR );
$ Command-> bindParam (": email", $ email2, PDO: PARAM_STR );
$ Command-> execute (); the bindParam () method is very similar to bindValue. The only difference is that the former uses a PHP variable to bind a parameter, while the latter uses a value. For the big data block parameters in the memory, the performance should be considered first.

For more information about binding parameters, see related PHP documents.

7. bind columns

When obtaining the query result, you can also bind the column with the PHP variable. In this way, the latest value is automatically filled with each row in the query result.

$ SQL = "SELECT username, email FROM tbl_user ";
$ DataReader = $ connection-> createCommand ($ SQL)-> query ();
// Bind the first column (username) with the $ username variable)
$ DataReader-> bindColumn (1, $ username );
// Bind the second column (email) with the $ email variable)
$ DataReader-> bindColumn (2, $ email );
While ($ dataReader-> read ()! = False)
{
// $ Username and $ email contain the username and email in the current row
}

8. use the table prefix

From version 1.1.0, Yii provides integrated support for table prefix usage. A table prefix is a string that is added before the name of a data table in the currently connected database. It is often used in a shared server environment. in this environment, multiple applications may share the same database. different table prefixes are used to differentiate each other. For example, one application can use tbl _ as the table prefix, and the other can use yii _.

To use the table prefix, set the CDbConnection: tablePrefix attribute to the expected table prefix. Then, {TableName} is used in the SQL statement to represent the table name, 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 the table prefix, we can use the following code to execute user-related queries:

$ SQL = 'select * FROM {user }}';
$ Users = $ connection-> createCommand ($ SQL)-> queryAll ();