4

The site has a content type and this content type has two fields:

  • Text Field
  • Term Reference Field

There are thousands of nodes on this content type and I want to change some values (find/replace).

For example, I want to change all the "ABCD" strings with the "WXYZ" strings on the text fields in all the nodes.

Is it a good way to do this change via MySQL or is there any other preferable way? (Both text field change and taxonomy term replacement).

Note: I saw the Search and Replace Scanner module but it gives some errors I couldn't understand why. And it gives timeout error for a large amount of nodes.

  • 1
    The 'text field'-part of your question is clear to me, but is there anything in that term reference field that needs ay kind of updating? In other words: what's the relevance of that field in the context of this change-all operation that your question is about? – Pierre.Vriens Feb 24 '16 at 20:17
  • 1
    @Pierre.Vriens actually the important part of the question is the text-field part. I realised that there are other ways for finding and replacing the taxonomy term. Thanks and welcome :) – herci Feb 24 '16 at 20:22
  • 1
    aha, that confirms what I was thinking of as a possible "additional answer" ... Hang on a little more to see it show up ... – Pierre.Vriens Feb 24 '16 at 20:36
  • 1
    The important thing for me is that: I will apply this find and replace multiple times for multiple find and replace values. So, if the "solution" can provide a simple option old_value and new_value then it would be easy. I tried the @Frank H.'s solution and it works good for SMALL AMAOUNT of nodes, but for thousands of nodes it doesn't work as expected (timeout errors etc). I "prepared" a custom module by using the BATCH API but it also gives some error and I will post it as another question. – herci Feb 24 '16 at 20:41
  • 1
    Ok @herci ... it took a little time to prepare my answer, but now it's up to you to review it, and if you think it's worth doing so also "experiment with it" ... Curious for your feedback later on! – Pierre.Vriens Feb 25 '16 at 20:23
4

The Drupal way is to iterate over the nodes of interest and modify the fields. Here is a simple example that shows how you could make modifications in the Body field of the Article content type:

  $nodes = node_load_multiple(array(), array('type' => 'article'));
  foreach($nodes as $node) {
    // Only modify nodes that have something in the body field
    if (isset($node->body[$node->language][0]['value'])) {

      $text = $node->body[$node->language][0]['value'];
      $find = 'ABCD';
      $replace = 'WXYZ';
      $text = str_replace($find, $replace, $text);
      $node->body[$node->language][0]['value'] = $text;
      node_save($node);
    } 
  }

One thing to note about doing it this way is the last-modified time of all your nodes will be updated. You may or may not want this. If you want to update the nodes without changing the timestamp, use this instead:

  $nodes = node_load_multiple(array(), array('type' => 'article'));
  foreach($nodes as $node) {
    // Only modify nodes that have something in the body field
    if (isset($node->body[$node->language][0]['value'])) {

      $text = $node->body[$node->language][0]['value'];
      $find = 'ABCD';
      $replace = 'WXYZ';
      $text = str_replace($find, $replace, $text);
      $node->body[$node->language][0]['value'] = $text;
      unset($node->created);
      unset($node->updated);
      field_attach_update('node', $node);
    } 
  }

Edit: How to modify taxonomy terms

You could use the same approach to modify taxonomy terms, i.e. load the terms, iterate over them, and do a find/replace on each.

  $vocab = taxonomy_vocabulary_machine_name_load('my_vocabulary');
  $terms = entity_load('taxonomy_term', FALSE, array('vid' => $vocab->vid));

  foreach ($terms as $term) {
    $text = $term->name;
    $find = 'ABCD';
    $replace = 'WXYZ';
    $text = str_replace($find, $replace, $text);
    $term->name = $text;
    taxonomy_term_save($term);
  }

Depending on how expert you are with MySQL, you could try updating the database field directly and you might have success, but it's prone to error especially if your fields have revisions.

  • 1
    Thanks a lot for your answer. I will try the "Drupal way". – herci Feb 5 '16 at 20:29
  • 1
    You're welcome. Updated my answer to also show how to modify taxonomy terms. – Frank H. Feb 5 '16 at 20:44
  • 1
    @herci The syntax error was caused by a typo in the line preceding the call to field_attach_update ("node-updated" should have read "node->updated". I have fixed it now. – Frank H. Feb 12 '16 at 17:24
  • 1
    The hook system in Drupal allows other modules to react to changes in data - lease of which, updating the last edit time, versioning, verification, and dozens more. Even though doing it the drupal way is significant slower - it is the right thing to do. – JP Dippenaar Feb 12 '16 at 18:28
  • 1
    @herci The 'Drupal way' (or at least, one Drupal way) to overcome timeout and memory errors is to use the Batch API. See also drupal.stackexchange.com/questions/57865/… – Frank H. Feb 12 '16 at 19:59
2

The real Drupal way of doing this is, writing an update script implementing hook_update_N(), which would find all the nodes with the text to be changed. Then update all the nodes in a batch (which prevents the timeouts).

A rough layout of the update script should look like as follows. Please read my comments in the code.

/**
 * Replaces all "ABCD" strings with "WXYZ" strings on text fields in all nodes.
 */
function mymodule_update_7100(&$sandbox) {
  // Set our $sandbox, which will stay persistent in multiple batch processes.
  if (!isset($sandbox['total'])) {
    // Find all nodes having 'ABCD' in a text field
    // Use your favorite method here, for the sake of this example I use EFQ.
    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'node');
    $query->propertyCondition('bundle', 'YOUR_CONTENT_TYPE');
    $query->fieldCondition('my_text_field', '%ABCD%', 'like');
    $result = $query->execute();

    // Put the node ids into the $sandbox
    $sandbox['nids'] = array_keys($result['node']);
    $sandbox['total'] = count($sandbox['nids']);
    // Set batch size to a number of nodes your server can process in a single
    // request. If you still get timeouts, try to reduce this number.
    $sandbox['batch_size'] = 10;
    $sandbox['current'] = 0;
  }
  else {
    // The sandbox is set, so we can process the nodes in batches. We load only
    // that much of nodes set in the $sandbox['batch_size']
    $offset = $sandbox['current'] * $sandbox['batch_size'];
    $current_nids = array_slice($sandbox['nids'], $offset, $sandbox['batch_size']);
    $nodes = node_load_multiple($current_nids);

    foreach ($nodes as $node) {
      // Change the text value here and save the node.
      // Again use your favorite method here. For the sake of this example, I
      // change the value directly on the $node object and save the $node.
      $text = $node->my_text_field['und'][0]['value'];
      $text = str_replace('ABCD', 'WXYZ', $text);
      $node->my_text_field['und'][0]['value'] = $text;
      node_save($node);
    }
  }

  // Check the progress and set $sandbox['#finished'] to 1 once all nodes are 
  // processed.
  $progress = $sandbox['current'] * $sandbox['batch_size'] / $sandbox['total'];
  $sandbox['#finished'] = $progress > 1 ? 1 : $progress;
}

Put above script in an .install-file of a custom module. You should adjust the name of the function. Please see the documentation of hook_update_N() if you are not familiar with update scripts.

Then, you run the update.php or use drush command drush updb. This update script will be called as many time as all the nodes are processed which are set in the first part of the if statement. On the first call, it will just find the nodes and put the node ids in the $sandbox. On the consequent calls, it will load the nodes in batches of 10 and process them.

  • Thanks a lot for your detailed answer. I will try this and if I succeed I will try to improve it for some special case (match whole world, multiple words to change etc.) – herci Feb 28 '16 at 18:41
2

Step 1: Create a Rules Component

Have a look at this example of a Rules Component (in Rules export format):

{ "rules_search_and_replace_values_in_a_text_field" : {
    "LABEL" : "Search and replace values in a text field",
    "PLUGIN" : "rule",
    "OWNER" : "rules",
    "REQUIRES" : [ "rules" ],
    "USES VARIABLES" : {
      "node" : { "label" : "Node", "type" : "node" },
      "old_value" : { "label" : "Old Value", "type" : "text" },
      "new_value" : { "label" : "New Value", "type" : "text" }
    },
    "IF" : [
      { "entity_has_field" : { "entity" : [ "node" ], "field" : "field_free_format_text" } },
      { "data_is" : {
          "data" : [ "node:field-free-format-text" ],
          "value" : "[old-value:value]"
        }
      }
    ],
    "DO" : [
      { "data_set" : {
          "data" : [ "node:field-free-format-text" ],
          "value" : "[new-value:value]"
        }
      },
      { "drupal_message" : { "message" : "String \u0022[old-value:value]\u0022 was replaced to string \u0022[new-value:value]\u0022 in field \u0022field_free_format_text\u0022 for node with id \u0022[node:nid]\u0022 and title \u0022[node:title]\u0022." } }
    ]
  }
}

Some more details to further explain the above above example:

  • This Rules Component will be used in the subsequent step.
  • In my case I have a text field with machine name field_free_format_text.

Note that I also added the Rules Condition Entity has field to make that field available for further processing in this Rules Component. That's also what is mentioned in the Rules UI when you're adding a "data comparison" condition: somewhere it says "The data selector helps you drill down into the data available to Rules. To make entity fields appear in the data selector, you may have to use the condition 'entity has field' (or 'content is of type')". For a video tutorial that illustrates the importance of this Entity has field condition, refer to Data types and data selection, especially what is shown between about 13:30 and 17:30 in that video.

Note that I've not added a Rules condition here about selected content types, that's possibly an extra condition you might want to add also. But if the selection list field is only used for the content types you're interested in, then the Entity has field condition should be sufficient already.

If you want, just grab (copy) the exported rule above, change all occurrences of the machine name of that field to match the machine name of your field, and then you can simply import this Rules Component in your own environment.

After you did this, you can navigate to admin/config/workflow/rules/components in your own site, and then use the hyperlink "execute" to qa-test it in your own site. After you use that link, use the button that says Switch to direct input mode. Then you'll be able to enter some Node identifier and hit the Execute mode. Depending on the node id you selected (if it has that field, and if the Rules Condition is satisfied), your node will be updated.

After you're confident your rule works perfect for all variations of your nodes, move on to the next step below.

Here is an example of the (2) Drupal messages I got when executing this Rules component for a node with ID = 48, in which I changed the string "Pierre" to "Herci":

  • String "Pierre" was replaced to string "Herci" in field "field_free_format_text" for node with id "48" and title "Demo field_free_format_text replacement".

  • Component rule: Search and replace values in a text field has been executed.

Variation of the display of that message could be that you use Rules to create a (similar) log record ...

Attention: you always get the 2nd message. But the first one only shows up if you have an actual match ... and hence the update gets performed also.

Step 2: Let VBO to the bulk of the work

This step is pretty easy ... Just create an appropriate view of all your nodes to be processed, and transform it into a Views Bulk Operations view. The clue to what you want VBO to do as an operation is ... you guessed it: Execute the Rules Component from Step 1.

If you're not familiar with how to do so, have a look at the video about Using Rules components with VBO". If you want even more, then also watch the subsequent "Using Rules components with more than one parameter" video.

Of course there are multiple variations of the above approach, like processing by component type, narrowing down your Views output to only "a" specific value, etc. But that should be straight forward, and up to your own imagination.

Attention: to process many nodes at once is quite a challenge (memory requirements, etc). Therefor you might want to also watch the video about "More details about VBO settings" ... Before you are tempted to use VBO for processing them all at once ...

Step 3: Straight forward variations

There are dozens (hundreds?) of straight forward variations of this question that can also be solved using the above combination of VBO and Rules. To just mention 1 of them, have a look at my answer to "How to bulk delete selected content/nodes?" ...

The flexibility you get with this approach also illustrates the advantages of this one, as compared to direct MySql approach.

Happy VBO-ing while Rules-ing!

  • 1
    Thanks a lot for your "could be a book itself" answer. I think you love the Rules module and I will become familiar with it also. Your answers not only answer the questions but also give some very interesting aspects for solving problems. Thanks again. – herci Feb 25 '16 at 20:25
  • 1
    Merci for the kudos, yes I "like" Rules ... even more: if you're familiar with "SAP", the programming there is done many times using "ABAP" as programming language ... To me "Rules" has become the ABAP-equivalent for Drupal ... combined with things like "Flag", "Group" (not "OG" !!!) and a few other modules I can get at least 95% of any site done ... with close to 0% of custom code ... That implies upgrading to new Drupal releases does not cause any custom coding pains either ... – Pierre.Vriens Feb 25 '16 at 20:28
  • @herci (FYI): A question like How to answer questions about getting started with Rules? seems like a possible followup-question about this one. I might soon start preparing my answer for it already ... – Pierre.Vriens Feb 25 '16 at 21:48
1

IMO the fastest way to do this is trough a direct SQL query to your database, because you don't have to use any of the Drupal API functions (that can make calls to another functions) to solve this.

Remember that you must reindex your site again because you change some values in your nodes.

But if you want get the revisions updated also, this is not the way to do it.

Edit after @kiamlaluno comment:

Be aware that if you make the changes with a SQL query and if you have some Rules/Triggers/Validations/etc, then these changes can't be detected.

  • 4
    If you use a SQL query to change a field value, you don't allow Drupal core and third-party modules to detect that change. Fast way doesn't mean correct way. – kiamlaluno Feb 5 '16 at 20:55
  • @kiamlaluno, could you please tell me what do you mean by "Drupal core couldn't detect that change". Most probably I will have to make this change through an SQL query because other methods didn't work me (timeouts, memory limits etc.). What happens if I change the values with that method? Thanks a lot. – herci Feb 12 '16 at 14:20
  • 2
    @herci Hooks are what allow other modules, including the ones part of Drupal core, to know something is going to be removed. Without them, modules cannot delete the data being invalidated by you altering nodes. – kiamlaluno Feb 12 '16 at 17:01
  • Hımm, OK trying to understand the terminology :) If I change the value of a text field through SQL query, then re-index the whole site is that a problem? It's not important for me Drupal understand the change, revision etc. The only important thing is Drupal should see the latest value of it. Do you think it's OK? Thanks for your answers. – herci Feb 12 '16 at 17:04
  • Even though this is "a" possible approach, I'd rather first ask the website owner "Do you want it badly?". However I can picture situations where this is technique is exactly what you want, such as when you want to mass update all sorts of things, but you don't want (eg) all sorts of eMails to go out to "subscribers" because of some update that nobody really cares to be informed about. Of course, that implies that if you're a huge enterprise, you'd also want to get approval from your auditors that there will NOT be any **audit trial" in the drupal loggings (to avoid being fired?). – Pierre.Vriens Feb 25 '16 at 22:15

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.