CODEWITHSUNDEEP
phpmysqlzend-framework
devrez
devrez

Reputation: 103

How to join tables in Zend Framework?

Hi I have my query in codeigniter since I I have switch to Zend Framework, I wanted do my query in zend framework:

Here is my sample query:

$sql = "SELECT Mes.fromid, Mes.is_read AS is_read, Mes.id AS mesid, Mes.message AS message, 
                 max(Mes.date) AS date, User.username AS username, User.id AS Uid
                FROM  `messages` AS Mes
                LEFT JOIN users AS User ON User.id = Mes.fromid
                WHERE Mes.toid = ? AND Mes.fromid <> ?
                GROUP BY Mes.fromid ORDER BY date DESC"; 

        $query = $this->db->query($sql, array($fromid, $fromid));

        return $query->result();

Thank you for you help.

Upvotes: 0

Views: 4798

Answers (3)

Iori
Iori

Reputation: 660

$select = $this->select()
            ->setIntegrityCheck(false)
            ->from(
                array('Mes' => 'messages'), 
                array('fromid', 'is_read AS is_read', 'id AS mesid', 'message AS message', 
                    'max(Mes.date) AS date')
            )
            ->joinLeft(array('User' => 'users'), "User.id = Mes.fromid", array('username AS username', 'id AS Uid'))
            ->where('mes.toid = ? ', $toid)
            ->where('mes.fromid = ?', $fromid)
            ->group('mes.fromid')
            ->order('date DESC');

Hope This helps :)

Upvotes: 2

Daya
Daya

Reputation: 1170

 $sql =  $db ->select()
             ->from('messages as mes',array('fromid','is_read','id as mesid','message','max(date) as date'))
             ->join('users as user','user.id = mes.fromid',array('username','id as uid'))
             ->where('mes.toid = ? ', $toid)
             ->where('mes.fromid = ?', $fromid)
             ->group('mes.fromid')
             ->order('date DESC');

Upvotes: 2

RockyFord
RockyFord

Reputation: 8519

Check out Zend_Db_Statement, it looks like you'll have to make very few edits to make this work.

Probably just change out the Db adapter to a Zend adapter and it'll probably work.

Upvotes: 0

Related Questions