zend framework where statement in query

Question

How can I use And/or in mysql queries using php and zend framework.for now I am using this :

    $db=Zend_Db_Table::getDefaultAdapter();
    $select=new Zend_Db_Select($db);
    $select->from('users','*');
    $select->joinInner('rest', 'users.repository = rest.id',array('username'));
    $select->where('username='.$rest.' and sold=0');
    return $db->fetchAll($select);

is that the correct way ? if not what is the correct way?

Answer 1

The following will fix your question, see PHP postgres Zend Framework Select query with WHERE 'AND' clause for a more complete writeup on how where() and orWhere() work.

$db=Zend_Db_Table::getDefaultAdapter();
    $select=new Zend_Db_Select($db);
    $select->from('users','*');
    $select->joinInner('rest', 'users.repository = rest.id',array('username'));
    $select->where('username= ?',$rest);//multiple where() will use AND operator in query
    $select->where('sold = ?', 0);//if you need the OR operator use the orWhere() method
    return $db->fetchAll($select);

To answer about the question marks (Excerpt from manual):

fetchAll($select, $value)//most of the Zend_Db methods follow this API

fetchAll() method: The first argument to this method is a string containing a SELECT statement. Alternatively, the first argument can be an object of class Zend_Db_Select. The Adapter automatically converts this object to a string representation of the SELECT statement

ie (? is a place holder for $value):
$select->where('id = ?', $id);
$select->orWhere('age > ?', $age);

You are currently using the old syntax, while it will still work it has been deprecated.

Answer 2

You can add AND's to your query by calling where() multiple times:

$select->where('this = ?', 'myValue')
       ->where('that = ?', 'myValue2');

This will translate into:

... WHERE this = 'myValue' AND that = 'myValue2'

To add one or more OR's to your query, use orWhere():

$select->where('this = ?', 'myValue')
       ->orWhere('that = ?', 'myValue2');

This will translate into:

... WHERE this = 'myValue' OR that = 'myValue2'

Note

Be sure to use the ? placeholder syntax as it is an easy way to prevent SQL injections.

Answer 3

That's one way, but you should be using ? placeholders for variables to protect against SQL injection:

$select=new Zend_Db_Select($db);
$select->from('users','*');
$select->joinInner('rest', 'users.repository = rest.id', array('username'));
$select->where('username = ? and sold=0', $rest);

(do the same for 'sold' if this sometimes also comes from a PHP variable.)

You can also chain together where clauses with multiple where() calls or orWhere() if you want or instead of and:

$select=new Zend_Db_Select($db);
$select->from('users','*');
$select->joinInner('rest', 'users.repository = rest.id', array('username'));
$select->where('username = ?' $rest);
$select->where('sold = ?', 0);

Since Zend_Db_Select uses a fluent influence you can also write it like this:

$select=new Zend_Db_Select($db);
$select->from('users','*')
       ->joinInner('rest', 'users.repository = rest.id', array('username'))
       ->where('username = ?' $rest)
       ->where('sold = ?', 0);