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?
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.
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);
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.
