php - Magento SQL Query - Filter products by category

755

I've the following SQL query to retrieve products:

$query = "SELECT *,m.m_name,m.m_website FROM belvg_countdown c 
INNER JOIN catalog_product_entity e ON e.entity_id=c.entity_id
LEFT JOIN manufacturers_products mp ON c.entity_id=mp.product_id
LEFT JOIN manufacturers m ON mp.manufacturers_id=m.manufacturers_id
WHERE DATE_FORMAT(expire_datetime_off , '%Y-%m-%d') > DATE_FORMAT(NOW() , '%Y-%m-%d')
AND DATE_FORMAT(expire_datetime_on , '%Y-%m-%d')!=DATE_FORMAT(NOW() , '%Y-%m-%d')
AND entity_type='product'";

Now I'd also add a statement to load only products that are in a category named "featured". Can you please help me to understand what should I compare/query to get only "featured" products?

Thanks.

Answer

Solution:

See below URL it is very help full to you

Magento ->addCategoryFilter - filtering product collection by root category

or try it

OK, I think this works, haven't tested too much but seems to have done the trick. You need to first get your stores root category id, then join some fields so you have access to the products "category_id", then filter using that:

$_rootcatID = Mage::app()->getStore()->getRootCategoryId();

$_testproductCollection = Mage::getResourceModel('catalog/product_collection')
->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
->addAttributeToFilter('category_id', array('in' => $_rootcatID))
->addAttributeToSelect('*');
$_testproductCollection->load();

foreach($_testproductCollection as $_testproduct){ 
    echo $this->htmlEscape($_testproduct->getName())."<br/>"; 
};

502

Answer

Solution:

You don't need all this query to get active product from specificy category. Use collection.

$producs = Mage::getModel('catalog/category')->load(#categoryid or $_category->getId())
            ->getProductCollection()
            ->addAttributeToSelect('*')
            ->addAttributeToFilter('is_saleable', 1)
            ->joinField('is_in_stock', 'cataloginventory/stock_item', 'is_in_stock', 'product_id=entity_id', '{{table}}.stock_id=1', 'left');

People are also looking for solutions to the problem: PHP bulk rename, with more than one renaming function

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question