Should I change multiple text fields values via MySQL or is there any other preferable way?

Question

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.

Answer 1

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.

Answer 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.

Answer 3

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!

Answer 4

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.