Retrieve products with a specific attribute value

Almost all Magento Models have a corresponding Collection object that can be used to fetch multiple instances of a Model.

To instantiate a Product collection, do the following

$collection = Mage::getModel('catalog/product')->getCollection();

Products are a Magento EAV style Model, so you'll need to add on any additional attributes that you want to return.

$collection = Mage::getModel('catalog/product')->getCollection();

//fetch name and orig_price into data
$collection->addAttributeToSelect('name');     
$collection->addAttributeToSelect('orig_price');

There's multiple syntaxes for setting filters on collections. I always use the verbose one below, but you might want to inspect the Magento source for additional ways the filtering methods can be used.

The following shows how to filter by a range of values (greater than AND less than)

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');     
$collection->addAttributeToSelect('orig_price');       

//filter for products whose orig_price is greater than (gt) 100
$collection->addFieldToFilter(array(
        array('attribute'=>'orig_price','gt'=>'100'),
));    

//AND filter for products whose orig_price is greater than (lt) 130
$collection->addFieldToFilter(array(
        array('attribute'=>'orig_price','lt'=>'130'),
));

While this will filter by a name that equals one thing OR another.

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');     
$collection->addAttributeToSelect('orig_price');       

//filter for products who name is equal (eq) to Widget A, or equal (eq) to Widget B
$collection->addFieldToFilter(array(
        array('attribute'=>'name','eq'=>'Widget A'),
        array('attribute'=>'name','eq'=>'Widget B'),           
));

A full list of the supported short conditionals (eq,lt, etc.) can be found in the _getConditionSql method in lib/Varien/Data/Collection/Db.php

Finally, all Magento collections may be iterated over (the base collection class implements on of the the iterator interfaces). This is how you'll grab your products once filters are set.

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');     
$collection->addAttributeToSelect('orig_price');       

//filter for products who name is equal (eq) to Widget A, or equal (eq) to Widget B
$collection->addFieldToFilter(array(
        array('name'=>'orig_price','eq'=>'Widget A'),
        array('name'=>'orig_price','eq'=>'Widget B'),          
));

foreach ($collection as $product) {
        //var_dump($product);
        var_dump($product->getData());
}

Source : http://stackoverflow.com/