Yii Access database Operations (Yii Dao)

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

You need to establish a database connection before accessing the database, and use DAO to create an abstract database link:
$connection = new Cdbconnection ($DSN, $username, $password);
$connection->active = true; The connection can only be used if it is activated
To close the connection, you can do this:
$connection->active = false;

Cdbconnection inherits from Capplicationcomponent, so he can use it anywhere, like a component. So you can access it like this:
Yii::app ()->db

Executing an 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 completely written by itself, you can do this:


$newSQL = ' Write down your surprising SQL statement ';


$command->text= $newSQL;


The Cdbcommand object has two methods execute () for non query SQL execution, and query (), which is commonly used for select queries


Execute () returns the number of rows affected by the INSERT, UPDATE and delete operation


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


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


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


$value = $command->queryscalar (); Query and return the field

Query () Returns the object representing the result set rather than the direct result, so the record to get the result set can be as follows:
$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 the iterator interface so you can use the foreach traversal
foreach ($dataReader as $row)
Return all records at once (array)
$rows = $dataReader->readall ();

The Queryxxx () method directly returns a matching collection of records, and when query () is not, he returns an object representing 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, rollback 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 it.
$transaction->commit ();
catch (Exception $e) {
$transaction->rollback (); Rolling back in exception handling
}

//Execute SQL, generally need to bind some user parameters, for user parameters, the need to prevent SQL injection attacks
//PDO Object binding parameters method to prevent SQL injection attacks, also extended from PDO DAO also has such a feature
//example:
/ /first, establish a connection:
$connection = Yii::app ()->db;
//second, write an invincible SQL statement, such as:
$sql = INSERT into Tbl_user (username, Emai L) VALUES (: Username,:email) ";
//Third, create a Cdbcommand object to execute SQL
$command = $connection->createcommand ($sql);
//Next, replace the formal argument in the SQL statement with the actual argument
$command->bindparam (": Username", $username, PDO::P Aram STR);  //This is a bit different from PDO, PDO without a colon
$command- >bindparam (": Email", $email, PDO::P Aram STR);   //also
//Finally, execute
$command->execute ();
/ /If you have additional data to insert, you can bind the argument again.

Use the Bindcolumn () method of the Cdbdatareader object to bind the columns in the result set to PHP variables.
Therefore, reading a row of records, the column values are automatically populated in the corresponding PHP object
Like this:
$connection = Yii::app ()->db;
$sql = "Select username, email from tbl_user";
$dataReader = $connection->createcommand ($sql)->query (); It's a great chain of methods, but it can't 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
Then loop through and manipulate the data
while ($dataReader->read ()!== false) {
...//differs from the previous while ($row = $dataReader->read ())!==false) Oh!
}

Sets the table prefix, using the Cdbconnection::tableprefix property to set in the configuration file
//

Yii implements the ability to completely dismember a complete SQL statement, such as this:
$user = Yii::app ()->db->createcommand (); Ha, I like the way chain!
->select (' ID, username, profile ')
->from (' Tbl_user u ')
->join (' Tbl_profile p ', ' u.id=p.user_id ')
->where (' Id=:id ', Array (': Id ' => $id)
->queryrow (); Returns the first row of a matching result set
In fact, this statement can be written like this: $newSQL = ' SELECT ID, username, profile tbl_user u INNER JOIN tbl_profile p on u.id = p.user_id WHERE U.id =:id '
The Dismemberment method has made me bored ....

Yii provides a mechanism for building SQL (that is, not having to write a long SQL yourself)


Prime Minister to instantiate a Cdbcommand object


$command = Yii::app ()->db->createcommand (); Note that the parameter is blank ...


The list of available methods is as follows:


->select (): SELECT clause


->SELECTDISTINCT (): SELECT clause, maintaining uniqueness of records


->from (): Building FROM clause


->where (): Building a WHERE clause


->join (): Constructing a INNER JOIN clause in the FROM clause


->leftjoin (): Building a LEFT JOIN clause in the FROM clause


->rightjoin (): Building the RIGHT join clause in the FROM clause


->crossjoin (): Adding Cross-query fragments (no use)


->naturaljoin (): Add a natural connection 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 (): Part II 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 a table qualification, or use an alias
Select (' tbl_user.id, username name ');
Or use an array as a parameter
Select (Array (' ID ', ' count (*) as Num '));

Use form () 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 a table alias, and you can use the full database qualified name
From (' Tbl_user u, public.tbl_profile P ');

WHERE clause
Using and in the Where ()
Where (Array (' and ', ' id=:id ', ' username=:username '), Array (': Id ' => $id, ': Username ' => $username);
Use OR in the Where () the same as and, as follows: # #看起来比直接写更加繁琐 # #
Where (Array (' and ', ' type=1 ', Array (' or ', ' id=:id ', ' username=:username ')), Array (': Id ' => $id, ': Username ' =>$ username));
In operator usage
Where (Array (' in ', ' ID ', array (1,2,3))
Like usage
Where (Array (' Like ', ' name ', '%tester% '));
Where (Array (' Like ', ' name '), Array ('%test% ', '%sample% '))//equals name like '%test% ' and name like '%sample%
It is a suicidal act to use this method in such a complicated way.

$keyword =$ get[' Q '];
Escape% and characters
$keyword =strtr ($keyword, Array ('% ' => ' n% ', ' => ' n '));
$command->where (' Like ', ' title ', '% '. $keyword. %'));

Add so much that you don't know what the synthesized SQL looks like, you can use->text to view (Magic method)
If you feel the combination of SQL is not wrong, then execute him, add->queryall (); This gets all the matching result sets.
Of course, if you are certain that only one row of the result set is executed, you can add->queryrow () to get it 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 ();
$command->reset (); Clean up the previous query
$posts = $command->select (' * ')->from (' tbl_posts ')->queryall ();

Yii's SQL Build function is a chicken ribs.

Active Record
Using AR to Access database in object-oriented way, AR realizes ORM Technology
When the Post class represents the table Tbl_post, we can insert a piece of data in such a way
$post = new post ();
$post->title = ' new title ';
$post->content = ' new content ';
$post->save (); Save is inserted

The most typical function of AR is to perform crud operations
DAO is positioned to solve complex database queries, while AR is positioned to solve simple database queries
An AR class represents a data table, and an Ar object represents a row of true records in a table, and the Ar class inherits Cactiverecord.
If you have a post table ' Tbl_post ', you can define an AR class like this
Class Post extends Cactiverecord
{
public static function model ($className = __class__)
{
Return Parent::model ($className);
}

Public Function Tablname ()
{
Return ' {{post}} ';
}
}

Each field in the table is represented by a property in the Ar class, and an exception is thrown if an attempt to access the table through the property does not have a field.

An AR must have a primary key, and if a table doesn't have a primary key, you can forge one in the class, like this:
Public Function PrimaryKey ()
{
return ' ID '; ' ID ' is a field in the associated table, but he is not a primary key, and now it is specified as the primary key
}

Instantiate an AR, fill in information (similar to populating user-submitted information), and then save
$post = new Post;
$post->title = ' sample post ';
$post->content = ' content for the ' sample post ';
$post->create_time = time ();
$post->save (); Save/Insert

Reading Records through AR Fine () findbypk () findbyattributes () Findbysql ()
$post =post::model ()->find ($condition, $params); Returns the Post object (if there is a matching record), otherwise null
$post =post::model ()->findbypk ($postID, $condition, $params);
$post =post::model ()->findbyattributes ($attributes, $condition, $params);
$post =post::model ()->findbysql ($sql, $params);

One example of

//Find ():
$post =post::model ()->find (' postid=:p ostid ', Array (':p ostid ' =>10));
//If the query condition is very complex, You will use the Cdbcriteria class
$criteria = new Cdbcriteria;
$criteria->select= ' title ';
$creteria->condition= ' postid=:p Ostid ';
$criteria->params=array (':p ostid ' =>10);
$post =post::model ()->find ($criteria);  //No second argument required
//Another better way to do it
$post =post::model ()-> Find Array (
        ' select ' => ' title ',
         ' condition ' => ' postid=:p ostid ',
        ' params ' => Array (':p ostid ' =>)
       ));

//If you are looking for multiline records, you can use FindAll () FINDALLBYPK () findallbyattributes () findallbysql ()
//Find all rows satisfying the spec ified condition
$posts =post::model ()->findall ($condition, $params);
//Find all rows with the specified Primary keys
$posts =post::model ()->findallbypk ($postIDs, $condition, $params);
//Find all rows with the Specified attribute values
$posts =post::model ()->findallbyattributes ($attributes, $condition, $params);
/ /Find all rows using the specified SQL statement
$posts =post::model ()->findallbysql ($sql, $params);
///If no matching Row, an empty array is returned, which can be used to detect the

empty ().

//Other available methods:
//Get the number of rows satisfying the specified condition
$n =post::model ()->count ($condi tion, $params);
//Get the number of rows using the specified SQL statement
$n =post::model ()->countbysql ($sql, $params);
// Check if there is at least a row satisfying the specified condition
$exists =post::model ()->exists ($condition, $para MS);

//using AR to update records
//A typical example:
$post =post::model ()->findbypk;
$post->title= ' new post title ';
$ Post->save (); Save the change to database
//How do you know if this is a new record or an old one? Use the following method:
if (Cactiverecord::isnewrecord)
//update the rows matching the specified condition
Post::model () ; UpdateAll ($attributes, $condition, $params);
//Update the rows matching the specified condition and primary key (s)
Post::model ()->updatebypk ($PK, $attribut ES, $condition, $params);
//Update counter columns in the rows satisfying the specified conditions
Post::model ()->updatecounters ($count ERs, $condition, $params);

Delete a record
$post =post::model ()->FINDBYPK (10); Assuming there is a post whose ID is 10
$post->delete (); Delete the row from the database table
Note that when a record is deleted, the $post is still available and the original data is preserved.
Class-Level methods
Delete the rows matching the specified condition
Post::model ()->deleteall ($condition, $params);
Delete the rows matching the specified condition and primary key (s)
Post::model ()->deletebypk ($PK, $condition, $params);

Data validation
To save user-submitted data to an AR object
$post->title = $_post[' title '];
$post->content = $_post[' content '];
$post->save ();

Assume $ post[' POST '] is a array of column values indexed by column names
$post->attributes=$ post[' post '];
$post->save ();

Rar:relatived Actie Record
RAR is essentially the execution of relational data queries

How to make an AR associate with another AR
4 Type of relationship
Self::belongs_to
Self::has_many
Self::has_one
Self::many_many

Relationship name (relationship type, class name to associate, foreign key name, additional options);
//Definition Table Relationship class: Post
Public Function relations ()
{
    return array (
   &NB sp;    ' author ' =>array (self::belongs_to, ' User ', ' author_id '),    / Returns the user object
        ' categories ' =>array (self::many_many, ' Category ', ' Tbl_ Post_category (post_id, category_id) '),
   );
}
//class: User
Public Function relations ()
{
    return array (
   &NB sp;        ' posts ' => array (self::has_many, ' Post ', ' author_id '),
             ' profile ' => array (self::has_one, ' profile ', ' owner _id ')
   );
}

After the relationship between AR is defined, the AR associated with AR is automatically instantiated when the relational query is executed, such as:
$author = User::model ()->findbypk (1);
$author->posts; Posts relationship has been defined.

Execute Relationship Query
1). Lazy loading approach lazy relationship execution
Retrieve the post whose ID is 10
$post =post::model ()->FINDBYPK (10);
Retrieve the post ' s author:a relational query'll be performed here
$author = $post->author; If it has not been done before, it is only now that the relationship query is executed (it is really lazy to do this step). )

Returns an array that will be null or empty if the relationship query does not have a matching result after execution.

2. Eager loading approach enthusiastic relationship inquiries//The name is really very Meng!
That means retrieving all the records you want in one lump sum. If you want, this, this is too enthusiastic!
$posts =post::model ()->with (' author ')->findall ();
SQL => ' SELECT tbl_post.*, author.* from Tbl_post t INNER JOIN tbl_user author on t.author = Tbl_user.id '

$posts =post::model ()->with (' Author ', ' Categories ')->findall ();
SQL => ' SELECT * tbl_post t INNER join Tbl_user u on t.author = u.id INNER JOIN categories C on t.id = C.post_i D

$posts =post::model ()->with (
' Author.profile ',
' Author.posts ',
' Categories ')->findall ();

$criteria =new Cdbcriteria;


$criteria->with=array (


' Author.profile ',


' Author.posts ',


' Categories ',


);


$posts =post::model ()->findall ($criteria);


Or


$posts =post::model ()->findall Array (


' With ' =>array (


' Author.profile ',


' Author.posts ',


' Categories ',


)


);

If we want to know who has posted the post, and the status of the post is "open". We don't care about the content of posts that users have posted.


$user = User::model ()->with (' posts ')->findall ();


' VS '


$user = User::model ()->with (Array (


' Posts ' => array (


' SELECT ' => false,


' JoinType ' => ' INNER JOIN ',


' Condition ' => ' posts.published = 1 '


),


)


)->findall ();


The return will be all users who have posted posts (and posts have been exposed)

Define more complex relationships in Relatinos ()


Class User extends Cactiverecord


{


Public Function relations ()


{


Return Array (


' Posts ' =>array (Self::has MANY, ' Post ', ' author id ',


' Order ' => ' posts.create time DESC ',


' With ' => ' categories '),


' Profile ' =>array (Self::has One, ' profile ', ' owner ID '),


);


}


}

Resolving ambiguity with aliases
$posts =post::model ()->with (' comments ')->findall (Array (
' Order ' => ' t.create time, comments.create time '
));

Dynamic relational query dynamically relational SQL queries, changing default SUSSU conditions:
User::model ()->with (Array (
' Posts ' =>array (' order ' => ' posts.create time ASC '),
' Profile ',
))->findall ();

$user =user::model ()->findbypk (1);
$posts = $user->posts (Array (' condition ' => ' Status=1 ')); Returns an AR object, not a data

Statistics Query
Class Post extends Cactiverecord
{
Public Function relations ()
{
Return Array (
' Commentcount ' =>array (self::stat, ' Comment ', ' post_id '),
' Categorycount ' =>array (self::stat, ' Category ', ' Post_category (post_id, category_id) '),
);
}
}