[FIXED] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

Issue

I am using the following query, but when it is running then getting mysql syntax error. I am using cakephp 3 framework. Error is generating ->where([DATE('Bookings.created') => date("Y-m-d")]) for this condition, so can you suggest how can avoid this issue.

$this->loadModel('ServiceManager.Bookings');
            $queryTodayAppointment = $this->Bookings->find('all')
                ->select(['Users.fullname', 'Bookings.slot_time'])
                ->where([DATE('Bookings.created') => date("Y-m-d")])
                ->contain(['Users'])
                ->order(['Bookings.id' => 'DESC'])
                ->limit(5);

Solution

Assuming you actually have to use the DATE function there are many ways to implements this.

simplest one (that's what you were already trying to do but you simply forgot to quote the function)

->where(["DATE(Bookings.created)" => date("Y-m-d")])

But you could also use cake SQL functions (reference)

$query = $this->Bookings->find();
$date = $query->func()->date(['Bookings.created' => 'identifier']);
$query->select(['Users.fullname', 'Bookings.slot_time'])
    ->where(function ($exp, $q) use($date) {
        return $exp->eq($date, date("Y-m-d"));
    })
    ->contain(['Users'])
    ->order(['Bookings.id' => 'DESC'])
    ->limit(5);

Answered By - arilia