Yii-How to write this query in yii?

Question

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.

thepiyush13 · Accepted Answer · 2015-12-02 20:01:37Z

1

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"];
}

edited Dec 2, 2015 at 20:01

answered Dec 2, 2015 at 19:43

thepiyush13

1,3211 gold badge8 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Usman Iqbal Over a year ago

How can I echo the result ? I want to show the result. So i used foreach loop like this. foreach($list as $l){echo $l;} and I got error, array to string conversion.

thepiyush13 Over a year ago

see my updated answer and don`t forget to include this part : select count(review) as result from .....

Usman Iqbal Over a year ago

I am following this link, yiiframework.com/doc/guide/1.1/it/database.query-builder But i did not find the way you told me above.

Usman Iqbal Over a year ago

oh wait a second please

Usman Iqbal Over a year ago

Well before marking your answer correct, I want you to please explain me the above code, if you dont mind.

|

MKR · Accepted Answer · 2019-09-27 09:24:48Z

0

Try this,

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

answered Sep 27, 2019 at 9:24

MKR

463 bronze badges

Collectives™ on Stack Overflow

Yii-How to write this query in yii?

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related