CODEWITHSUNDEEP
phpcakephpcakephp-2.3
user1104854
user1104854

Reputation: 2167

MySQL query for dates with CakePHP

I'm trying to select the values that fall between 2 dates, so I'll have to use <= and >=, however, my query is seemingly behaving as just less than or greater than and not considering values equal to the date.

I'm using CakePHP which saves dates in "Y-m-d h:i:a" format. I wanted to find dates on given week intervals (starting on Sundays), so I used the following.

$start_date = date('Y/m/d', strtotime('last Sunday', strtotime($timestamp)));
$formatted_start_date = str_replace("/","-",$start_date);

I tried to do find $start_date formatted as "Y-m-d" but then it wouldn't find the correct date, so I switched it to the way it is and used str_replace to format it to using "-" instead of "/".

$date_query = $this->Order->query("select * from orders where id = '$id' and created => '$formatted_start_date' and created <= '$current_timestamp' ");

Considering the time values in my database are in "Y-m-d h:i:a" format, can I use "Y-m-d" for date comparison? Is it possible to do a MySQL query that involves both LIKE and <= ?

Upvotes: 0

Views: 8356

Answers (4)

Ashish Pathak
Ashish Pathak

Reputation: 824

Try this in your controller

$searchTutorQuery = $this->Tutordetails->find('all', array(
                'conditions' => array(
                    "User.zip" => $zipcode1,
                    "Tutordetails.user_id"=>$uid,
                    "Tutordetails.subject_id" => $subjectName,
                    "Tutordetails.hourly_rate >=" => $hourly_rate 


                ),
            //"User.id =" => $userid,
            'fields' => array('user_id', 'Tutordetails.subject_id', 'Tutordetails.hourly_rate',
                'User.zip', 'Tutordetails.zip'),
            'order' => array('Tutordetails.id DESC')
        ));

Upvotes: 0

Dave
Dave

Reputation: 29121

No need to do a str_replace() - just get the Y-m-d:

$start_date = date('Y-m-d', strtotime('last Sunday', strtotime($timestamp)));

Then, instead of manually creating a query, use the CakePHP conventions (yes, you can use Y-m-d for date comparison even though the datetimes stored in the database are Y-m-d H:i:s)

$this->Order->find('all', array(
    'conditions' => array(
        'id' => $id,
        'created >=' => $start_date,
        'created <=' => $end_date . ' 23:59:59',
        'my_field LIKE' => '%whatever%'
));

Though - this seems kind of strange - usually you're either looking for something by 'id' OR by a date range - not both. But - maybe you have a reason :)

And as you can see above, yes, you can add a 'LIKE' also if you need.

Upvotes: 9

shihab mm
shihab mm

Reputation: 525

Just add condition's array like this.

$resl = $this->DBNAME->find('all',array(conditions=>array('date1>date','date1<date')));

Replace 'date' with your date.
This is worked for me.

Upvotes: 0

johhniedoe
johhniedoe

Reputation: 173

Above answer is totally correct, but you can take easier approach using cakePHP time helper, which has function daysAsSql, it transcribes and time-readable strings into database range.

http://book.cakephp.org/2.0/en/core-libraries/helpers/time.html#TimeHelper::daysAsSql

Upvotes: 0

Related Questions