CODEWITHSUNDEEP
phpmysqlzend-frameworkpdo
Sinisa Valentic
Sinisa Valentic

Reputation: 552

Zend db PDO not returning result

i ahve a problem with the folowing:

$sql = "SELECT e.*, c.name city_name, u.*, IF(l.item IS NULL, 0, 1) as iLike, DATE_FORMAT(e.date, '%h:%i') hour, DATE_FORMAT(e.date, '%d/%c/%Y') day,  DATE_FORMAT(e.date, '%c') month
FROM `events` e
LEFT OUTER JOIN `like` l ON l.item = e.uuid AND l.user = :user
            LEFT OUTER JOIN city c ON c.city = e.city
            LEFT OUTER JOIN user u ON u.user = e.user
            WHERE
             e.deleted = 0
            AND DATE(e.date) >= CURDATE()
            AND e.user = :user
            ORDER by e.date ASC
            ";
    $select = $eventsModel->getAdapter();
    $select->setFetchMode(Zend_Db::FETCH_ASSOC);
    $prepare = $select->query($sql, array('user'=>$this->user_id));

This code returns results if i hardcode :user to the query. Example:

$sql = "SELECT e.*, c.name city_name, u.*, IF(l.item IS NULL, 0, 1) as iLike, DATE_FORMAT(e.date, '%h:%i') hour, DATE_FORMAT(e.date, '%d/%c/%Y') day,  DATE_FORMAT(e.date, '%c') month
            FROM `events` e
            LEFT OUTER JOIN `like` l ON l.item = e.uuid AND l.user = 23
            LEFT OUTER JOIN city c ON c.city = e.city
            LEFT OUTER JOIN user u ON u.user = e.user
            WHERE
             e.deleted = 0
            AND DATE(e.date) >= CURDATE()
            AND e.user = 23
            ORDER by e.date ASC
            ";
    $select = $eventsModel->getAdapter();
    $select->setFetchMode(Zend_Db::FETCH_ASSOC);
    $prepare = $select->query($sql);

This only started happening when we moved our code to the new server, the new OS is Debian 6, squeze, mysql 5.5, PHP 5.3.21.

Thank you,

Upvotes: 0

Views: 219

Answers (1)

sdespont
sdespont

Reputation: 14025

Use ? instead :user, and $this->user_id instead of array('user'=>$this->user_id)

Furthermore, you can't bind data in JOIN statement, so l.user = e.user

$sql = "SELECT e.*, c.name city_name, u.*, IF(l.item IS NULL, 0, 1) as iLike, DATE_FORMAT(e.date, '%h:%i') hour, DATE_FORMAT(e.date, '%d/%c/%Y') day,  DATE_FORMAT(e.date, '%c') month
            FROM `events` e
            LEFT OUTER JOIN `like` l ON l.item = e.uuid AND l.user = e.user
            LEFT OUTER JOIN city c ON c.city = e.city
            LEFT OUTER JOIN user u ON u.user = e.user
            WHERE
             e.deleted = 0
            AND DATE(e.date) >= CURDATE()
            AND e.user = ?
            ORDER by e.date ASC
            ";
    $select = $eventsModel->getAdapter();
    $select->setFetchMode(Zend_Db::FETCH_ASSOC);
    $prepare = $select->query($sql, array($this->user_id));

Upvotes: 1

Related Questions