php - Magento SQL Query - Filter products by category


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?





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')
->addAttributeToFilter('category_id', array('in' => $_rootcatID))

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



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())
            ->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


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

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.