Running sql in a php file from cron

[ApplianceDepot]

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

[Tom Robertshaw · Accepted solution]

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

[ApplianceDepot]

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

Cheers

Tony