CODEWITHSUNDEEP
phpmysqlsqldatabaseyii
Usman Iqbal
Usman Iqbal

Reputation: 2429

Yii-How to write this query in yii?

I am trying to fetch the no of records, but I am unable to write this query in yii. My sql query is given below.

select count(review) from review_business where (date_created>=DATE_FORMAT(NOW() ,'%Y-11-01')) and (date_created<=DATE_FORMAT(NOW() ,'%Y-12-01')) . I am currently writing this query in yii is given below.

$results=Yii::app()->db->createCommand()
        ->Select('count(review)')
        ->from('review_business') 
        ->where('date_created'>=DATE_FORMAT(NOW() ,'%Y-11-01'))
        ->queryAll();

But I am getting this error Fatal error: Call to undefined function NOW() in G:\www\ba.dev\protected\views\business\stats.php on line 19. I am sure it is because of my poor yii query. Kindly correct my query.

Upvotes: 0

Views: 201

Answers (2)

MKR
MKR

Reputation: 46

Try this,

$results=Yii::app()->db->createCommand()
    ->Select('count(review)')
    ->from('review_business') 
    ->where('date_created >=DATE_FORMAT(NOW() ,"%Y-11-01")')
    ->queryScalar();

Upvotes: 0

thepiyush13
thepiyush13

Reputation: 1331

If you are willing to run the entire query and not use the active record pattern You can try built-in YII commands to do that. 

$query = 'select * from post where category=:category';
$list= Yii::app()->db->createCommand($query)->bindValue('category',$category)->queryAll();

Explanation: $query should be obvious and =:category is binding the variable category dynamically to the query for security reasons. In next line I am creating the query and substituting the value of category variable by using bindValue() function, finally queryAll retrieves all the records in the database. Hope it is clear now. In your case 

$query = "select count(review) as result from review_business where (date_created>=DATE_FORMAT(NOW() ,'%Y-11-01')) and (date_created<=DATE_FORMAT(NOW() ,'%Y-12-01'))" ;
$list= Yii::app()->db->createCommand($query)->queryAll();

Now you can access the result like this:

foreach ($rows as $row) {
  $result = $row["result"];
}

Upvotes: 1

Related Questions