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

Yii - My Journey

When I was asked to write a web application I looked at the various frameworks around for PHP and decided to use Yii. This is relatively new framework compared to other more mature frameworks however it has a very active user base.

Starting off with any framework can be a very steep learning curve and choosing one is almost just as hard. I had some slight experience with the ZEND Framework however I found it very overwhelming. There is a mountain of documentation however getting started with this Framework I found difficult. I had to write a lot of code to do something simple. I played with CakePHP a few years ago and I found it to be really rigid. If you wanted to deviate from the Cake conventions then you pay the price. I had no experience with Code Igniter however after reading an article by SHELDMANDU I decided to give Yii a try.

Now don't be fooled. If you are not experienced with PHP and have little Object Oriented Programming experience then Yii is not going to be a breeze however even for a novice PHP developer you can get a lot done in a short amount of time with very little effort. 

Yii is based on the Model View Controller architecture. Now there is no point me explaining this when there is so much written about it however for a good overview have a look at the Yii website. In a nutshell the MVC architecture separates the data and logic into the Model, the View contains the actual layout and forms and the Controller acts as a conduit between the Model and the View. It is really worth understanding this concept.

So how do you get started using the Yii Framework. In my opinion you can read and read (and read) however until you start writing the code you do not learn anything. I would recommend that you go to the Yii website and download Yii then in my opinion head on over to Larry Ullman's website and do the 8 part tutorial called "Introduction to the Yii Framework". It is the best (in my opinion) beginners tutorial that covers everything from how to install the framework to writing a basic Employee/Department application. Larry Ullman writes in easy to understand language and does not leave out big chunks of information that you have to figure out for yourself like some other tutorials. This 8 part tutorial will allow you to develop a small application quickly and you will be on your way to using the Yii Framework.

Where do you go from there? I would recommend you write an application for yourself. Think of an application you would like to develop and jump in feet first. You can read the "Definitive Guide to Yii" and I would recommend at some stage you do however it still comes back to the fact (or opinion) that writing code is the only way to learn. The Yii forums are really worth using when you get stuck (and you will) because the user base is very active and more experienced Yii users are very happy to help and give advice. Every time I had a problem I could either find the answer in the forum or someone brighter than me would solve my problem. Do not be afraid to ask for help.

Where is this blog going to go->I found that the same questions seem to get asked over and over (maybe in a different way but the concept is usually the same) so I thought I would post the problems I had when I first started in the hope that it will help others. It is a real shift from writing desktop applications to web applications and hopefully this blog will help people starting out with the Yii Framework.