Run SQL from PHP file

[ApplianceDepot]

Hi All, We're trying to automate categories being disabled when there are no active products in them and re-enable when products are added or made active.... So we've found this nice sql query and it works brilliantly but we're trying to convert it to run correctly and safely from a php file so it can then be added to our cron job.... Can anyone show us how to run the following code in php?

UPDATE `catalog_category_entity_int` AS `status`
INNER JOIN `eav_attribute` AS `attr` ON `attr`.`attribute_code` = 'is_active'
AND `attr`.`entity_type_id` = 3
AND `status`.`attribute_id` = `attr`.`attribute_id`
SET `status`.`value` = IF((SELECT COUNT(`index`.`product_id`)
FROM `catalog_category_product_index` AS `index`
WHERE `index`.`category_id` = `status`.`entity_id` GROUP BY `index`.`category_id`) > 0, 1, 0)
WHERE `status`.`store_id` = 0

[Damian Culotta]

Hi @ApplianceDepot,

What if you create a cronjob inside Magento, as a module.

You'll need to create the model. For example, something like /app/code/local/vendor/module/Model/Cronjob.php

class Vendor_Module_Model_Cronjob
{

    public function runMyCronJob()
    {

        //Your code here

    }

}

Then you'll ne set the cronjob on the /app/code/local/Vendor/Module/etc/config.xml

<config>
    ...
    <crontab>
        <jobs>
            <my_module_my_cronjob>
                <run>
                    <model>module/cron::runMyCronJob</model>
                </run>
                <schedule>
                    <cron_expr>0 * * * *</cron_expr>
                </schedule>
            </my_module_my_cronjob>
        </jobs>
    </crontab>
</config>

This means you cronjob will be executed everyhour at the minute 0.

Now, you can use this code on your method:

public function runMyCronJob()
{

    $connection = Mage::getSingleton('core/resource')->getConnection('write');

    $connection->query("YOUR SQL QUERY");

}

This is not perfect but I guess it will point you to the right direction.

Now the job is part of your own store.

[ApplianceDepot]

Sounds like a better way to run it rather than in standard cron, just wondering would it be sane for me to add an observer for when products are saved and then initiate the category check? That way we wouldnt have to wait for the cron to run for category's that now have active products to be activated, or would this slow things down too much ? 

[Damian Culotta]

I guess the problem could be related with the amount of products and how fast those products change.

Maybe you can observe the events realted with the cataloginventory and not with catalog.

I guess you can investigate some event like:

<cataloginventory_stock_item_save_commit_after>

And only if the product changed from one state to another, you can perform the query. (I'm not sure abotu performance here... you should test how many times the query would be executed)