YII Database related Operations

Cdbconnection: An abstract database connection
Cdbcommand:sql statement
Cdbdatareader: Matching a row of result set records
Cdbtransaction: Database Transactions

A database connection needs to be established before accessing the database; Creating an abstract database link using DAO

1 $connection New Cdbconnection ($dsn$username$password); 2 $connection true;   // You can use the connection only if you have activated it 3 $connection false;  // Close Connection

Cdbconnection inherits from Capplicationcomponent, so he can use it anywhere, like a component. So that you can access

Yii::app ()->db

The Execute SQL statement requires the Cdbcommand object, which is returned by Cdbconnection::createcommand (), so

$connection=yii::app (),db; $command=$connection->createcommand ($sql);

If the SQL statement wants to be written entirely by itself, you can

$newSQL = ' sql statement '; $command->text=$newSQL;

The Cdbcommand object has two methods execute () for non-query SQL execution, and query (), in layman's words, is used for select queries
Execute () returns the number of records rows affected by the INSERT, UPDATE and delete operations
Query () returns a Cdbdatareader object that uses the Cdbdatareader object to traverse all records in the matching result set

$rowCount=$command->execute ();//Execute the non-query SQL$dataReader=$command->query ();//execute a query SQL//return Cdbdatareader to Image$rows=$command->queryall ();//Query and return all rows of result$row=$command->queryrow ();//Query and return the first row of result$column=$command->querycolumn ();//Query and return the first column of result$value=$command->queryscalar ();//Query and return the first field in the first row

Query () Returns the object that represents the result set rather than the immediate result, so the record to get the result set can be

$dataReader=$command-query ();
//Cdbdatareader::read () can fetch one row of data at a time and return false at the end while(($row=$dataReader->read ())!==false) {...}
//Cdbdatareader implements an iterator interface so you can use the foreach traversalforeach($dataReader  as $row) {...}
//return all records at once (array)$rows=$dataReader->readall ();

The Queryxxx () method returns a matching set of records directly, and when query () is not, he returns an object that represents the result set

The Cdbtransaction class in Yii is used for transactions

//first, establish a connection$connection= Yii::app ()db;//second, start the transaction$transaction=$connection-BeginTransaction ();//third, execute SQL, and if the error throws an exception, roll back in exception handling. Try{$connection->createcommand ($sql 1),execute ();$connection->createcommand ($sql 2),execute ();    //.... Other SQL executions//If SQL execution does not throw an exception, commit. $transaction-commit ();} Catch(Exception $e) {    $transaction->rollback ();//rolling back in exception handling}

In the execution of SQL, it is generally necessary to bind some user parameters, for user parameters, to prevent SQL injection attack
The method of binding parameters of a PDO object prevents SQL injection attacks, and DAO, which is also extended from PDO, has this function
To illustrate:

//first, establish a connection:$connection= Yii::app ()db;//Second, write the Invincible SQL statement, for example:$sql= "INSERT into Tbl_user (username, email) VALUES (: Username,:email)";//third, create a Cdbcommand object to execute the SQL$command=$connection->createcommand ($sql);//Next, replace the formal parameter in the SQL statement with the actual argument$command->bindparam (": Username",$username, PDO::P Aram STR);//This is a bit different from PDO, with no colon in PDO$command->bindparam (": Email",$email, PDO::P Aram STR);//similarly//Finally, the implementation$command-execute ();//If there is additional data that needs to be inserted, you can bind the argument again. 

Use the Bindcolumn () method of the Cdbdatareader object to bind the columns in the result set to the PHP variable.
Therefore, reading a row of records, the column values are automatically populated into the corresponding PHP object
such as this:

$connection= Yii::app ()db;$sql= "Select username, email from tbl_user";$dataReader=$connection->createcommand ($sql)->query ();//Great method chain, unfortunately cannot be followed. each ()$dataReader->bindcolumn (1,$username);//The first column value is bound to $username$dataReader->bindcolumn (2,$email);//The second column value is bound to $email//and then the data is looped and manipulated while($dataReader->read ()!==false ) {    ...//differs from the previous while (($row = $dataReader->read ())!==false) Oh! }

Set the table prefix, using the Cdbconnection::tableprefix property in the configuration file

// Yii implements the ability to completely dismember a complete SQL statement, such as: $user = Yii::app ()->db->CreateCommand ();             ->select (' ID, username, profile ')        ->from (' Tbl_user u        '),join(' Tbl_ Profile P ', ' u.id=p.user_id ')        array(': id ' =$id)        //  Returns the first row of a matching result set

In fact, the statement is this: $newSQL = ' SELECT ID, username, profile from tbl_user u INNER JOIN tbl_profile p on u.id = p.user_id WHERE u.id =:id '

Yii provides a mechanism for building SQL (i.e. not writing long SQL yourself)
PM to instantiate a Cdbcommand object

$command // Note The parameters are left blank. 

The list of available methods is as follows:

->select ():SELECT clause->selectdistinct ():SELECT clause and maintains the uniqueness of the record->from ():build FROM clause->where ():building a WHERE clause-Join(): Build inner in the FROM clauseJOINclauses->leftjoin ():construct a LEFT JOIN clause in the FROM clause->rightjoin ():building a RIGHT join clause in the FROM clause->crossjoin ():Add cross-query fragments (unused)->naturaljoin ():Add a natural connection sub-fragment->group ():GROUP BY clause->having ():a clause similar to where, but used with GROUP by->order ():ORDER BY clause->limit ():the first part of the limit clause->offset ():the second part of the limit clause->union (): Appends a union query fragment

Select () returns all columns by default

// but you can do this:Select (' username, email '); // or use the table qualifier, or use the alias Select (' tbl_user.id, username name '); // or use an array as the parameter Select (array(' id ', ' count (*) as Num '));

From () If you have more than one table, you need to use a comma-delimited string, just like a native SQL statement:

From (' Tbl_user, Tbl_post, Tbl_profile '); // of course, you can also use table aliases, and you can also use the full database-qualified name from (' Tbl_user u, public.tbl_profile P ');

WHERE clause

// in Where (), useand where (arrayarray(': id ' = '$id, ': username ' = =$username ); // use OR in the Where () is the same as and usage, as follows:  # #看起来比直接写更加繁琐 # #arrayarray (' OR ', ' id=:id ', ' username =:username '),array(': id ' = '$id, ': username ' =$username ));

In operator usage

Where (arrayarray()))

Like usage

whereArray(' Like ', ' name ', '%tester% ') ; where (Array(' Like ', ' name ',Array('%test% ', '%sample% ')) )//equals the name like '%test% ' and the name '%sample%//' so complicated to go on, using this method is simply suicidal behavior. $keyword=$ get[' q '];//Escape% and characters$keyword=STRTR($keyword,Array('% ' = ' n% ', ' ' = ' = ' n '));$command->where (Array(' Like ', ' title ', '% '.$keyword.‘ %‘));

Add so much, you don't know what the SQL is like after the synthesis, you can use ->text () to view (Magic method)
If you feel that the combined SQL has no errors, then execute him and add ->queryall (); This will get all the matching result sets.
Of course, if you are sure that there is only one row in the result set of execution, you can add ->queryrow (); To get directly.
If a Cdbcommand object needs to be executed more than once, remember to call Reset () before the next execution.

$command = Yii::app ()->db->CreateCommand (); $users $command->select (' * ')->from (' tbl_users '),Queryall (); Reset/ Clean up the previous query$posts$command $command ->select (' * ')->from (' tbl_posts ')->queryall ();

YII Database related Operations