is this possible? I can't restructure the tables over again every time I need to run a certain dependent function.

        $catSQL = "SELECT *
        FROM `catalog_product_entity_int`
        WHERE `attribute_id` =96 //our status attribute id
        AND `value` =2"; //disabled value

This gets all disabled products. However, this table is indexed by Id. I'm passed a number of sku's, how can I merge this table with something like catalog_product_entity and search by an array of skus? Essentially I need to get the Id of all disabled products and then check their quantities in the stock_item table.

Thanks to Marius below i've acomplished the above with the following:

function getDisabledIdsFromSkus($arraySkus){
        $conn = Mage::getSingleton('core/resource')->getConnection('core_write');

        $ComponentSkustring = $this->format($arraySkus);

        $anotherRoute = "SELECT k.`qty`
        FROM `catalog_product_entity` i
        LEFT JOIN catalog_product_entity_int e ON i.entity_id = e.entity_id
        RIGHT JOIN cataloginventory_stock_item k ON i.entity_id = k.item_id
        WHERE e.`attribute_id` =96
        AND e.`value` =2
        AND i.`sku` IN ($string)
        ORDER BY i.`sku` ASC ";

        $componentQuantities = ($conn->fetchAll($anotherRoute));
        Mage::log(print_r($componentQuantities, true));

private function format($array)
    // initialize variable
    $formattedArray = array();

    //for each element
    foreach ($array as $string) {
        //append quotes
        $formattedArray[] = "'" . $string . "'";

    //convert formatted elements to string and return
    $formattedString = implode(',', $formattedArray);
    return $formattedString;

1 Answer 1


You cannot get the disabled products on frontend area when the flat tables are on.
The indexer that populates the flat tables takes into account only enabled products and associated to the websited being indexed.

[EDIT] Try with this select

FROM `catalog_product_entity_int` i
LEFT JOIN catalog_product_entity e ON e.entity_id = e.entity_id
WHERE i.`attribute_id` =96
AND i.`value` =2
AND e.sku IN ('sku1', 'sku2')
  • I've updated my question, please take a look. Mar 27, 2015 at 12:54
  • Seems like it'll work, along with the sku array filtering, but I got a "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 88 bytes)..." when I ran the above. Mar 27, 2015 at 13:11
  • well...you are running magento with 128M memory. The minimum required is 256M and the recommended value is 512M.
    – Marius
    Mar 27, 2015 at 13:15
  • Ah, I see. How would I go about further filtering this by an array of skus? Mar 27, 2015 at 13:21
  • @easymoden00b. I've updated the query
    – Marius
    Mar 27, 2015 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.