Yii Framework + MySQL Part 4 ( More on active record queries, debugging, limits, conditions, ordering )
posted by kevin on July 13, 2009
Related Articles

Now we'll look into getting back our active records and displaying the data they contain.

Lots of times I'm in the middle of working with some data and I want to output my current data to easily see what I have. It's a lot of times easier than looking at my database and table structure.

Pre-Yii I was used to getting data back in arrays, so it was easy enough to just do this:

 
<pre>
<?php
$product_arrays = runQueyFetchAllResults("SELECT * FROM products");
print_r($product_arrays);
?>
</pre>
 

Now this doesn't work well if you would do the same with our model object.

 
<pre>
<?php
$Products = Product::model()->with(array('brand'))->findAll();
print_r($Products);
?>
</pre>
 

This would output an array taking up about 500 lines on your screen. It's not what you want at all.

This will output the array of data for your first Product model in the Products array.

 
<pre>
<?php
$Products = Product::model()->with(array('brand'))->findAll();
print_r( $Products[0]->getAttributes() );
?>
</pre>
 
Adding External Libraries to Yii

Now this is where I'd probably add to my toolkit (Which I easily ported right into my Yii install some helper functions that I use use often, and i would just pass it my Products array and it will go through the items and output my attributes.

To import another framework, or toolkit all you have to do is add the folder of your toolkit into the protected directory.

So for example, my toolkit, CA_Framework, will reside at: /data/web/vhosts/kevinkorb.com/protected/CA_Framework/

Now I have to tell Yii that I want to add that folder to be available for Lazy Loading.

Open up the main configuration file, you will see the import array: protected/config/main.php

 
    'import'=>array(
        'application.models.*',
        'application.components.*',
        'application.CA_Framework.*',
    ),
 

So as you can probably guess, the 'application.' = 'protected/'.

So now here would be a quick sample, the file is at: 'protected/CA_Framework/CA_Debug.php'

 
<?php
/**
 * Debug
 *
 * @author Kevin Korb
 */
Class CA_Debug {
 
    /**
     * Output Array
     * 
     * A wrapper function for print_r that provides our
     * <pre></pre> tags because the three lines just to 
     * output our array in a nice fashion is just painful.
     *
     * @param array $array
     */
    public static function output_array($array) {
        echo "<pre>";
        print_r($array);
        echo "</pre>";
    }
 
    public static function output_yii_models($model_or_array) {
        if(is_object($model_or_array)) {
            self::output_array($model_or_array->getAttributes());
        }
        else {
            $array = array();
            foreach($model_or_array AS $model) {
                $array[] = $model->getAttributes();
            }
            self::output_array($array);
        }
    }
 
}
 
 

Now in my application I can just call: And that will work with a single model object, or an array of model objects.

 
<?php 
$Products = Product::model()->with(array('brand'))->findAll();
CA_Debug::output_yii_models($Products);
?>
 

Output

 
Array
(
    [0] => Array
        (
            [id] => 3
            [brandId] => 1
            [name] => 1990 Telecaster (Mexico)
            [price] => 899.99
        )
 
    [1] => Array
        (
            [id] => 4
            [brandId] => 1
            [name] => 1965 Telecaster
            [price] => 4950.00
        )
 
)
 
 

Now now that we can easily test our data.

Lets add a condition now.

 
$Products = Product::model()->with(array('brand'))->findAll("price > 900");
CA_Debug::output_yii_models($Products);
 
Result: As expected.
Array
(
    [0] => Array
        (
            [id] => 4
            [brandId] => 1
            [name] => 1965 Telecaster
            [price] => 4950.00
        )
 
)
 
 

Now Yii also makes it really easy to prepare your statements and bind your parameters.

For example, lets get the product array for prices based on user given data.

 
//User given price of 1000
$price = 1000;
$Products = Product::model()->with(array('brand'))->findAll("price > :price", array(':price' => $price));
 

Pretty straightforward, but for me it sometimes gets a little hard to look at and understand it, especially when it gets very complex, so I like to break like this:

 
$Products = Product::model()
    ->with(array('brand'))
    ->findAll("price > :price",
        array(':price' => $price));
 

But that's up to you. The findAll takes 2 parameters, the first is the condition, the second is an associative array that matches to the parameters in the prepared statement.

So for example, to do a similar query and you wanted to change it so they give a price range it would be:

 
$price_low = 1000;
$price_high = 5000;
 
$Products = Product::model()
    ->with(array('brand'))
    ->findAll("price > :price_low AND price < :price_high", array (
        ':price_low' => $price_low,
        ':price_high' => $price_high )
    )
);
 

Now, we'll move on to queries with limits and other more complex queries.

Yii has a class called 'CDbCriteria' which we'll instantiate and assign the properties to match what we would normally query. After using it a little bit I found it to be very simple to use and made the queries easy to read.

Using CDbCriteria

CDbCritera contains 9 public properties that you will set and your query will be built from that data.

The properties are:

 
condition
group
having
join
limit
offset
order
params
select
 

The properties should look VERY familiar to you if you've ever used SQL. And if you haven't, this tutorial isn't really for you.

Examples of Using CDbCriteria

Basic Usage
 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Products = Product::model()->findAll($Criteria);
 

OR

 
//An example using the constructor to populate the properties.
$Criteria = new CDbCriteria(array('condition' => 'price > 30'));
$Products = Product::model()->findAll($Criteria);
 

Personally, I like to go with the first approach. I think it's generally easier to read, but that's just my personal preference.

Adding A Limit
 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Products = Product::model()->findAll($Criteria);
 
Limit with Offset
 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Products = Product::model()->findAll($Criteria);
 
Ordering Results
 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Products = Product::model()->findAll($Criteria);
 
Limiting Selected Fields
 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Criteria->select = "id, name";
$Products = Product::model()->findAll($Criteria);
CA_Debug::output_yii_models($Products);
 
Notice the output of the code above. 
 
Array
(
    [0] => Array
        (
            [name] => 1990 Telecaster (Mexico)
            [id] => 3
            [brandId] => 
            [price] => 
        )
 
)
 
 

So that made me curious at if I accidentally selected certain fields from the DB and then tried to change an attribute and save it to see if the non-selected values would be empty.

This is what I did.

 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Criteria->select = "id, name";
 
$Products = Product::model()->findAll($Criteria);
CA_Debug::output_yii_models($Products);
foreach($Products AS $Product) {
    CA_Debug::output_yii_models($Product);
    $Product->name = "changed";
    $result = $Product->save();
    var_dump($result);
    CA_Debug::output_yii_models($Product);
}
 

Here was my output:

 
Array
(
    [0] => Array
        (
            [id] => 3
            [name] => 1990 Telecaster (Mexico)
            [brandId] => 
            [price] => 
        )
 
)
 
Array
(
    [id] => 3
    [name] => 1990 Telecaster (Mexico)
    [brandId] => 
    [price] => 
)
 
bool(false)
 
Array
(
    [id] => 3
    [name] => changed
    [brandId] => 
    [price] => 
)
 
 

Now when I looked at my logger, I notice that it never even tried to save my model. So Yii must be smart enough to know whether it can attempt the save. Now I'm suprised the logger didn't tell us that it didn't let the save happen because of whatever...

But after I thought about it, it was probably looking at my rules that were setup and the data didn't meet the validation rules so it probably didn't run the query and just sent back false.

So I changed what I was running to fill in the rest of the data and save it to see what happend:

 
$Criteria = new CDbCriteria();
$Criteria->condition = "price > 30";
$Criteria->limit = 1;
$Criteria->offset = 1;
$Criteria->order = "name ASC";
$Criteria->select = "id, name";
 
$Products = Product::model()->findAll($Criteria);
CA_Debug::output_yii_models($Products);
foreach($Products AS $Product) {
    CA_Debug::output_yii_models($Product);
    $Product->name = "changed";
    $Product->brandId = 2;
    $Product->price = 2500;
    $result = $Product->save();
    var_dump($result);
    CA_Debug::output_yii_models($Product);
}
 

Now this DID change my record. So just know that when you're using the 'select' in your active record finds then you might possibly be removing data from your tables if the rules aren't set properly. So just beware of that.

Using CDbCriteria with Prepared Statements.

 
$Criteria = new CDbCriteria();
$Criteria->condition = "brandId = :brandId";
$Criteria->params = array (
    ':brandId' => 1
);
$Products = Product::model()->findAll($Criteria);
 
Multiple parameters
 
$Criteria = new CDbCriteria();
$Criteria->condition = "brandId = :brandId AND price > :price";
$Criteria->params = array (
    ':brandId' => 1,
    ':price' => 500,
);
$Products = Product::model()->findAll($Criteria);
 

Now you can still add in your relations to eager load on these...

I.E.

 
$Criteria = new CDbCriteria();
$Criteria->condition = "brandId = :brandId";
$Criteria->limit = 1;
$Criteria->params = array (
    ':brandId' => 1
);
$Product = Product::model()->with(array('brand'))->findAll($Criteria);
CA_Debug::output_yii_models($Product);
CA_Debug::output_yii_models($Product[0]->brand);
 
 
Array
(
    [0] => Array
        (
            [id] => 4
            [brandId] => 1
            [name] => 1965 Telecaster
            [price] => 4950.00
        )
 
)
 
Array
(
    [id] => 1
    [name] => Fender
    [website] => http://www.fender.com
)
 
 

And that the data was all returned using one query.

 
Querying SQL: SELECT `Product`.`id` AS `t0_c0`, `Product`.`brandId` AS
`t0_c1`, `Product`.`name` AS `t0_c2`, `Product`.`price` AS `t0_c3`, t1.`id`
AS `t1_c0`, t1.`name` AS `t1_c1`, t1.`website` AS `t1_c2` FROM `Product` 
LEFT OUTER JOIN `Brand` t1 ON (`Product`.`brandId`=t1.`id`) WHERE (brandId
= :brandId) LIMIT 1
 
Tags: Yii Framework and MySQL, PHP, Yii Framework, CDbCriteria
Read more | Comments (17) | Last updated on July 8, 2009

17 comments to "Yii Framework + MySQL Part 4 ( More on active record queries, debugging, limits, conditions, ordering )"

#38
Fender says:
July 20, 2009 at 09:42 am
Your yii tuts are very helpfull thnx..
#44
Qiang says:
July 21, 2009 at 06:02 am
Very nice writing! One minor addition: when querying with a criteria, you can also use the following format: 
 
$products=Product::model()->findAll(array(
   'condition'=>...,
   'params'=>...,
   'select'=>...,
));
 
#45
Kevin Korb says:
July 21, 2009 at 10:59 am
@Qiang,

Thanks for pointing that out. I'll edit the post and add that in there.

Keep up the good work, your framework is by far my favorite PHP framework. I just received the new PHP Architect where they talked about 6 or 7 PHP frameworks and Yii was not listed. Maybe the new site design will boost popularity as people can be superficial. But it's also new, and I figure the more documentation, code samples, and tutorials, the better chance for adoption. :)
#64
Tim says:
September 19, 2009 at 05:49 pm
you know, you're tutorials helped me a lot, but what do you do when you have things that repeat themselves in the controllers? i ask the same stuff in more then one controller, so i extend the ar model with my own function?
#65
Kevin Korb says:
September 19, 2009 at 06:00 pm
@Tim,

Good question. Part 5 will cover putting everything together and adding to your Models to utilize the code reusability.
#76
piotrek says:
November 4, 2009 at 07:32 am
I'm just curious. What does 'CA_', in name of your 'CA_Debug' class name, mean? :) BTW, your tutorials are very usefull.
#77
Kevin Korb says:
November 4, 2009 at 08:54 am
@piotrek,
CA_ stands for 'Creative Anvil', At my job, we maintain our own toolkit that we use throughout the majority of our projects, so CA_ just lets the autoloader know that this class is going to the location where the CA_ classes are housed.
#95
Weboide says:
December 7, 2009 at 06:14 pm
Thank you for this post, this helped with the same problem about saving!
#109
Jonas says:
March 4, 2010 at 06:53 am
Thanks for this post, very nice summary for a realive Yii-newbie :-)
#169
James says:
August 20, 2010 at 01:34 pm
Thanks for the CA_Debug class, it's a very handy bit of functionality. I've used CakePHP before and that was a useful feature provided by the CakePHP framework. I missed that kind of result set debugging in Yii, so CA_Debug fills in very nicely. Thanks again.
#174
Chris says:
September 3, 2010 at 07:10 pm
Thanks for posting this. This is more helpful than than the official Yii docs. I learn better with examples, and your examples were good.
#182
champi says:
October 13, 2010 at 07:21 am
Thanks a lot for your tutorials they are very helpful. Is there a way we can use Yii's built in pagination for displaying results this way ? I mean using a foreach loop in the view instead of using clistview which paginates
#233
tom says:
December 16, 2010 at 10:32 am
Many thanks for this tutorial! I don't like the official docs because there's not much real example for the usage.
#241
Humbal says:
January 21, 2011 at 03:30 am
Thanx for you great effort. I am having problem with 3 tables. I want to access records from 3 tables under certain criteria. Suppose the table names are : tbl_message, tbl_messageto, tbl_user I have controller name message. Here I want to access the record of the particular user who has received the email. It's sql command is : SELECT * FROM tbl_member u, tbl_message m, tbl_messageto t WHERE u.id=$userId AND u.id=t.memberId AND m.messageId=t.messageId Can you give me idea to query records under the certain criteria?
#242
aaa says:
January 21, 2011 at 05:10 am
how to select value used in query for yii framework
#251
Nicolas R says:
February 19, 2011 at 07:43 am
How can export the SQL statement? I need to view the SQL to check if is correct? exists some Yii function?
#257
Janakiraman says:
March 1, 2011 at 05:22 am
Great work! I am newbie too yii framework and it helped me a lot in my development. Thanks for the great post. Keep on continuing.
Bookmark and Share

Leave a Comment

Your email address will not be published.

(You can enclose code in <php></php> blocks.)

You may use Markdown syntax.

Please enter the letters as they are shown in the image above.
Letters are not case-sensitive.