2

I am wondering what would be the safest way to search through the mysql database for a specific keyword and update.

More specifically here is what I am trying to do.

In many of the nodes we have videos embedded. Unfortunately, we didn't add preload="none" to our videos. Now the pages take a long time to fully load plus they download the whole video without the user clicking on it wasting bandwidth.

I want to search the nodes (mainly the body field) for: video controls="" and replace with video controls="controls" preload="none"

I assume I can use something like this? update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Which table should I search?

I don't want to mess up the database so a second opinion is highly appreciated.

  • The name of the table is whatever the field name is holding the data you are replacing, as well as its revision table. – Kevin Oct 2 '17 at 17:27
4

You don't say which version of Drupal you are using. If it's D7, there is the scanner module which can probably do what you want.

If it is D8, I'd suggest writing a drush script to update the content. The standardization of entities and methods makes that fairly easy. Here's a snippet of code from a script I used to grab all text from our site that needed to be translated. You can modify it to do what you need.

use \Drupal\node\Entity\Node;
use Drupal\Core\Entity\EntityInterface;

$entity_type = 'node';
$bundle = 'article;
$fields = [
  'field_header' => 'paragraph',
  'field_summary' => 'text',
];

$entities = gen_i18n_get_entities($entity_type, $bundle);
foreach ( $entities as $vid => $id ) {
  $entity = \Drupal::entityTypeManager()->getStorage($entity_type)->load($id);

  gen_i18n_parse_entity($entity, $fields, $page);
}

/**
 * Find the entity ids for the specified type and bundle.
 *
 * @param string $entity_type
 * @param string $bundle
 * @return array
 */
function gen_i18n_get_entities( $entity_type, $bundle ) {
  $query = \Drupal::entityQuery($entity_type)
    ->condition('type', $bundle);
  if ( $entity_type == 'node' ) {
    $query->condition('status', 1);
  }
  $results = $query->execute();
  return $results;
}
/**
 * Walk thru the entity fields to get required information.
 *
 * @param EntityInterface $entity
 * @param array $fields
 * @param array $page
 */
function gen_i18n_parse_entity( EntityInterface $entity, array $fields, array &$page ) {
  global $field_num;

  $field_num = 0;
  foreach( $fields as $field => $field_type ) {
    if ( !in_array($field_type, array('alias', 'menu'))) {
      $field_def = $entity->getFieldDefinition($field);
      if ( $field_def != NULL ) {
        $label = $field_def->getLabel();
      }
      else {
        print "** Could not find field {$field} in {$entity->bundle()} {$entity->id()}\n";
        continue;
      }
    }
    else {
      $label = $field_type;
    }
    switch ( $field_type ) {
      case 'text':
        gen_i18n_parse_text( $entity, $field, $page, $label);
        break;
      case 'paragraph':
        gen_i18n_parse_paragraph($entity, $field, $page, $label);
        break;
      case 'metatags':
        gen_i18n_parse_metatags( $entity, $field, $page, $label);
        break;
      case 'alias':
        $field_num++;
        $page['en']["{$field_num} - URL"] =  $entity->toURL()->toString();
        break;
      case 'menu':
        gen_i18n_parse_menu($entity, $field, $page);
        break;

    }
  }
}  
  • 1
    In D8, albeit a riskier method, but one when you don't know the names of the tables you are looking for would be to use a MySQL stored procedure: kevinquillen.com/drupal/2017/07/13/… -- it definitely has drawbacks, but I have used it successfully to update mass tables and field values instantly. – Kevin Oct 2 '17 at 19:36
  • New link: kevinquillen.com/… – ognockocaten Aug 5 at 3:01

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.