0

i have these two tables ,

s(id,firstname,lastname,mydate,mount,sh)

users(uid,email,password,nameoffice)

and this query

$sql="SELECT s.id,s.firstname,s.lastname,s.mydate,s.mount,users.nameOffice FROM sayer LEFT JOIN users ON s.sh=users.sh order by date(s.mydate) DESC,s.mount DESC";

how could I write this query on zend framework?

ulduz114
  • 1,150
  • 6
  • 21
  • 37

1 Answers1

3

One way would be as follows:

    $db = Zend_Db_Table::getDefaultAdapter();
    $select = $db->select();

    $select->from(array('s' => 'sayer'), array('id', 'firstname', 'lastname', 'mydate', 'mount'))
           ->joinLeft(array('u' => 'users'), 's.sh = u.sh', array('nameOffice'))
            ->order(array('date(s.mydate) DESC', 's.mount DESC'));

    print($select->assemble());

This results in:

    SELECT `s`.`id`, `s`.`firstname`, `s`.`lastname`, `s`.`mydate`, `s`.`mount`, 
   `u`.`nameOffice` FROM `sayer` AS `s` LEFT JOIN `users` AS `u` ON s.sh = u.sh 
    ORDER BY date(s.mydate) DESC, `s`.`mount` DESC

For more have a look at Zend_Db_Select.

Marcin
  • 215,873
  • 14
  • 235
  • 294
  • it works just when I put and empty array instead of array('nameoffice') , whats the problem when i use array('nameoffice') – ulduz114 May 21 '11 at 11:29
  • @ulduz114. I don't know. The assembled sql is the same as in your question. Do you get any errors or something? – Marcin May 21 '11 at 12:11
  • @ulduz114 You have written the wrong query. See nameOffice in the table. @Marcin may have taken from your query. You have provided the wrong information then how come you get the desired result ? Anyway hope you get the logic how to work on the scenario :) – Hari K T May 21 '11 at 17:57