cancel
Showing results for 
Search instead for 
Did you mean: 

Running sql in a php file from cron

SOLVED

Running sql in a php file from cron

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)
2 REPLIES

Re: Running sql in a php file from cron

You could create a script file in a new folder, say, tools/disable_categories.php. 

 

The code to wrap around your statement would be:

<?php

require_once "../app/Mage.php";

Mage::app();

$resource = Mage::getSingleton('core/resource');
$write_conn = $resource->getConnection('core_write');

$query = "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";

$write_conn->query($query);

 

 

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

Re: Running sql in a php file from cron

Thanks Tom, this is what i thought but wanted to check first. I'll give it a go now.

 

Cheers

 

Tony