Internal server error when programmatically updating about 100 nodes

Question

I'm writing a function that unpublishes a bunch of event-type nodes within a date range. My query pulls about 100 nodes, but I'm apparently maxing out the available memory. Here is the code in question:

$sql = "SELECT node.nid AS nid FROM node node  LEFT JOIN content_type_event node_data_field_event_date ON node.vid = node_data_field_event_date.vid WHERE (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') >= '2014-09-10' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') <= '2015-09-10') AND (node.type in ('event'))";

$nids = mysql_query($sql);
$report = "";

while ($nid = mysql_fetch_array($nids)) {
    $node_id = $nid['nid'];

    $node = node_load($node_id);

    $node->status = 0;

    node_save($node);
    $report = $node->title . " unpublished.";
}
drupal_set_message($report);

Any suggestions greatly appreciated.

Answer 1

Give your server more memory ;)

• Better to use EntityFieldQuery (but this will not speed up your script ..)
• You could replace the while loop with foreach (array_keys($result['node']) as $node_id)
• In the worst case you'll need to limit the $result or $nids array to 10 or something .. and run the script several times, maybe in cron ?

Or run an update query (also possible without php)

$sql = "UPDATE node node LEFT JOIN content_type_event node_data_field_event_date ON node.vid = node_data_field_event_date.vid SET node.status = 0 FROM WHERE (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') >= '2014-09-10' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') <= '2015-09-10') AND (node.type in ('event'))";

mysql_query($sql);