writing mysql query with zend framework

Question

i have this query that works fine

SELECT t.username
FROM users t LEFT JOIN friends y ON t.id=y.user_id2 and y.user_id1=2
WHERE LOWER(t.username) LIKE 'ha%'
ORDER BY 
    CASE WHEN y.user_id2 IS NULL THEN 1 
    ELSE 0 
    END     
    ,t.username;

i'm trying to write it with zend framework and this is what i came up with

        $users = new Users;
        $select = $users->select();
        $select->setIntegrityCheck(false);
        $select->from(array('t1' => 'users'), array('username'));
        $select->joinLeft(array('t2' => 'friends'), 't1.id=t2.user_id2 and t2.user_id1 =2');
        $select->where("LOWER(t1.username) like '$input%'");
        $select->order("t1.username, CASE WHEN t2.user_id2 IS NULL THEN 1 ELSE 0 END ");
        $listofusernames = $users->fetchAll($select);

however it seems not to work and i get this error

Fatal error: Uncaught exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: Unknown column 't1.username, CASE WHEN t2.user_id2 IS NULL THEN 1 ELSE 0 END ' in 'order clause'' in /opt/lampp/htdocs/vote_old/library/Zend/Db/Statement/Mysqli.php:77 Stack trace: #0

apparently it has to do with the case embedded in the order by clause.

do you have any idea how to fix that code ?

thank you

akond · Accepted Answer · 2011-07-25 06:29:02Z

3

try to put the columns in array like

$select->order(array('t1.username',
              new Zend_Db_Expr ('CASE WHEN t2.user_id2 IS NULL THEN 1 ELSE 0 END')));

edited Jul 25, 2011 at 6:29

akond

16.1k4 gold badges37 silver badges58 bronze badges

answered Jul 25, 2011 at 6:01

Haim Evgi

126k46 gold badges222 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Don Gorgon Over a year ago

Fatal error: Uncaught exception 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: Unknown column 'CASE WHEN t2.user_id2 IS NULL THEN 1 ELSE 0 END' in 'order clause''

Haim Evgi Over a year ago

you need to use Zend_Db_Expr, like stackoverflow.com/questions/2162709/…

Don Gorgon Over a year ago

i'm not sure what do u mean by that. can you be more specific please ?

Collectives™ on Stack Overflow

writing mysql query with zend framework

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related