[SOLVED]Query Builder problem

Yii 1.1 General Discussion
1

Hi, if i have a query like this




SELECT * FROM wsrecruitcvhead WHERE ResumeTitle LIKE '%ma%' AND ResumeSummaryIntroduction LIKE '%ma%'

AND DateCreated BETWEEN DATE_ADD(NOW(), INTERVAL -2 MONTH) AND DATE_ADD(NOW(),INTERVAL -1 DAY);

how to conver that to Query builder of Yii?

OR this one




SELECT ResumeTitle, ResumeSummaryIntroduction FROM wsrecruitcvhead WHERE ResumeTitle LIKE '%ma%' AND ResumeSummaryIntroduction LIKE '%ma%'

AND DateCreated BETWEEN DATE_ADD(NOW(), INTERVAL -2 MONTH) AND DATE_ADD(NOW(),INTERVAL -1 DAY);

I don’t know how to do it…i checked the manual there’s no “AND LIKE” operator for the where clause

2

Try this:


$criteria= new CDbCriteria;

$criteria->compare('ResumeTitle', $value, true); // the last parameter to true will do like

$criteria->addCondition('DateCreated BETWEEN DATE_ADD(NOW(), INTERVAL -2 MONTH)');

And so on.

3

I tried it like this




                $criteria = new CDbCriteria;

                $criteria->compare('ResumeTitle', $resumetitle, true); // the last parameter to true will do like

                $criteria->compare('ResumeSummaryIntroduction', $resumesummaryintroduction,true);

                $criteria->addCondition('DateCreated BETWEEN DATE_ADD(NOW(), INTERVAL -2 MONTH)');

                $data = Wsrecruitcvhead::model()->findAll($criteria);

I got an error




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

is there a way to convert the query from my first post into this query builder format of yii?

http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder#building-data-retrieval-queries

4

I was able to refactor it, but the problem now is the date, am getting error




                $data = Yii::app()->db->createCommand()

                     ->select('ResumeID, ResumeTitle, ResumeSummaryIntroduction')

                     ->from('wsrecruitcvhead')

                     ->where("ResumeTitle LIKE '%$resumetitle%'

                             AND ResumeSummaryIntroduction LIKE '%$resumesummaryintroduction%'

                             AND SummaryOfPositionSought LIKE '%$summaryofpositionsought%'

                             AND SummaryOfSkills LIKE '%$summaryofskills%'

                             AND DateCreated BETWEEN DATE_ADD(NOW, INTERVAL -1 DAY)")

                     ->queryAll();

error




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

any suggestions ?

5

Mysql Operator between




                $data = Yii::app()->db->createCommand()

                     ->select('ResumeID, ResumeTitle, ResumeSummaryIntroduction')

                     ->from('wsrecruitcvhead')

                     ->where("ResumeTitle LIKE '%$resumetitle%'

                             AND ResumeSummaryIntroduction LIKE '%$resumesummaryintroduction%'

                             AND SummaryOfPositionSought LIKE '%$summaryofpositionsought%'

                             AND SummaryOfSkills LIKE '%$summaryofskills%'

                             AND DateCreated BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW() ")

                     ->queryAll();
6

Excellent thanks alawt to both of you …problem solved! ;D