How to do a count of records in Yii

Incorporating a database into your website is a fairly standard requirement and Yii makes if easy. One really important section of the "Definitive Guide to Yii" is "Working with Databases" and I would encourage you to read this. Some of the syntax can be a little confusing and often time I found if I could see an example then it is relatively straight forward to extrapolate. Hopefully the example to follow may help you understand some of the syntax.

If you need to count how many records in your database match specific criteria then this is relatively simple to do.  Lets assume we have a table called 'job'  and a column called 'zipcode' and we want to count how many rows in the table have a zipcode = 4000. First you need to define the count property in your Model class before you can access or use the variable in your controller.

class Job extends CActiveRecord
{
//count for number of zips
public $zipcount;

Now in your controller you need to create the query and one way is create an instance of  CDbCriteria.  If your not confident with Object Oriented programming creating an instance just means we use the 'new' keyword to create an object.

$criteria = new CDbCriteria;
$criteria->select = 'zipcode, COUNT(zipcode) as zipcount';
$criteria->condition='zipcode=4000';
$myquery=Job::model()->find($criteria);

Notice the 'as zipcount'. This alias 'zipcount' must be the same as the variable name you declared in your model. Now to access this 'zipcount' variable in your controller all you need to use is: $myquery->zipcount

Sometimes there may be a more complex query and CDbCriteria is perfect. Lets assume in the Job table we want to know how many 'doctors' live in this zip code and lets assume that the column 'job_id' holds this value. Lets assume for a doctor, job_id=1. So to count how many doctors live in the zip code of 4000 we would use.

$criteria = new CDbCriteria;
$criteria->select = 'job_id, COUNT(job_id) as zipcount';
$criteria->condition='job_id=:jobId and zipcode=4000';
$criteria->params=array(':jobId'=>1);
$myquery=Job::model()->find($criteria);

I used  'params'  just for the purpose of example as it displays the syntax you need to use. It is useful when you do not have a hard-coded value but rather a variable. For example say the variable '$jobtype' holds a job description value you would use 'params' as

$criteria->condition='job_id=:jobId and zipcode=4000';
$criteria->params=array(':jobId'=>$jobtype);

That is the basic requirements if you need to do a count in the database. CDbCriteria allows you to create very complex queries and I would encourage you to have a read of the Yii Documentation for more information.

References:

1. http://www.yiiframework.com/forum/index.php/topic/7996-how-to-get-record-count/page__p__40356__hl__count+record+#entry40356
2. http://www.yiiframework.com/doc/guide/1.1/en/database.ar