cancel
Showing results for 
Search instead for 
Did you mean: 

Run SQL from PHP file

Run SQL from PHP file

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

Tags (2)
3 REPLIES

Re: Run SQL from PHP file

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.

--
If you've found one of my answers useful, please give "Kudos" or "Accept as Solution"

Re: Run SQL from PHP file

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 ? 

Re: Run SQL from PHP file

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)

--
If you've found one of my answers useful, please give "Kudos" or "Accept as Solution"