0

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.

1 Answer 1

0

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);
2
  • Thanks for this! I should have mentioned that I'm in an old D6 installation (I know, I know), so EntityFieldQuery isn't available. Sigh . . .
    – JOakland
    Apr 15, 2016 at 22:58
  • ah okey .. just lift the php limit thoe ... or run an update statement, see my edit
    – rémy
    Apr 15, 2016 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.