In this post we are discussing about how filtering data collection is done on magento

The most important method on a database Collection is addFieldToFilter. This adds your WHERE clauses. Consider this bit of code, run against the sample data database (substitute your own SKU is you’re using a different set of product data)

addFieldToFilter() = fields in sql “where” clause

$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('sku')
->addFieldToFilter('sku','123');
var_dump($products);

For more complex filtering, pass an array as the second argument to addFieldToFilter().
The following list of filters and their sql equivalents is from Alan Storm’s excellent article on collections:

addFieldToFilter('sku', array("eq"=>'123'))
WHERE (e.sku = '123')
 
addFieldToFilter('sku',array("neq"=>'123'))
WHERE (e.sku != '123')
 
addFieldToFilter('sku',array("like"=>'123'))
WHERE (e.sku like 'n2610')
 
addFieldToFilter('sku',array("nlike"=>'123'))
WHERE (e.sku not like '123')
 
addFieldToFilter('sku',array("is"=>'123'))
WHERE (e.sku is '123')
 
addFieldToFilter('sku',array("in"=>array('123', '456', '789')))
WHERE (e.sku in ('123', '456', '789'))
 
addFieldToFilter('sku',array("nin"=>array('123', '456', '789')))
WHERE (e.sku not in ('123', '456', '789'))
 
addFieldToFilter('sku',array("notnull"=>'123'))
WHERE (e.sku is NOT NULL)
 
addFieldToFilter('sku',array("null"=>'123'))
WHERE (e.sku is NULL)
 
addFieldToFilter('sku',array("gt"=>'123'))
WHERE (e.sku > '123')
 
addFieldToFilter('sku',array("lt"=>'123'))
WHERE (e.sku < '123')
 
/* Two ways to do greater than equal: */
 
addFieldToFilter('sku',array("gteq"=>'123'))
WHERE (e.sku >= '123')
 
addFieldToFilter('sku',array("moreq"=>'123'))
WHERE (e.sku >= '123')
 
addFieldToFilter('sku',array("lteq"=>'123'))
WHERE (e.sku <= '123')
 
addFieldToFilter('sku',array("finset"=>array('123')))
WHERE (find_in_set('123',e.sku))
 
addFieldToFilter('sku',array('from'=>'50','to'=>'100'))
WHERE e.sku >= '50' and e.sku <= '100'

AND

->addFieldToFilter('sku',array('like'=>'123%'))
->addFieldToFilter('sku',array('like'=>'456%'))
WHERE (e.sku like '123%') AND (e.sku like '456%')

OR

->addFieldToFilter('sku',array(array('like'=>'123%'),array('like'=>'456%')))
WHERE (((e.sku like '123%') OR (e.sku like '456%')))

Hope this bit of code helps you to filter data collection in magento