update image path with words starting uppercase to lowercase chars

Question

I have to convert the image path to lowercase characters inside wp_posts table.

Right now, I have inside my posts this:

<img class="alignleft" title="my image title" alt="my image alt" src="http://www.mydomain.com/wp-content/uploads/2012/09/My-Image-Lowercase-First-Char.jpg" width="75" height="75" />

And I need to update those uppercase chars in the image path to lowercase chars like this: my-image-lowercase-first-char.jpg.

Is it possible to do it via mysql in phpmyadmin?

Answer 1

Changing the case of the paths without changing the file names themselves could break your links, depending on the server and its configuration.

But to get to the question directly, you may be able to do this with SQL using MySQL's REPLACE and LOWER, but I would advise against it. MySQL has some regex functionality but not much. PHP's regex is better by far.

Pull your posts, loop over them, and fix the links. Run something like this:

function replace_cb($matches) {
  if (!empty($matches[1])) {
    return 'src="'.strtolower($matches[1]).'"';
  } else {
    return $matches[0];
  }
}
function reset_paths() {
  global $wpdb;
  // debug using a specific post ID
  // $posts = $wpdb->get_results("SELECT ID,post_content FROM {$wpdb->posts} WHERE ID = 128");
  // remove the where for a real run over all posts
  $posts = $wpdb->get_results("SELECT ID,post_content FROM {$wpdb->posts}");
  $pattern = '|src="([^"]+)"|';
  foreach ($posts as $p) {
    define( 'DOING_AUTOSAVE', true );
    $p->post_content = preg_replace_callback($pattern,'replace_cb',$p->post_content);
    wp_update_post($p);
  }
}
reset_paths(); // run the function

reset_paths(); beneath the function definition is what triggers it-- what actually runs the function.

But make a backup and test that thoroughly on a dev server with disposable data before you try it in production.

If you have a lot of posts you may have timeout or memory issues so watch that. If you do have problems you will have to run the function incrementally.

The assumption is that your markup is consistent. That will now replace image `URL's where the attribute is surrounded by single quotes.

Answer 2

I guess, you can use one of these plugins: 1. http://wordpress.org/plugins/search-and-replace/ 1. http://wordpress.org/plugins/search-regex/

Just do backup before modyfying your database ;)

The other way is to do it manually:

1. Export your database to SQL (with data).
2. Use text editor to find & replace all occurences of files (it would be nice to do it with some regular expressions, I guess)
3. Import your modified database to server.

Also don't forget to change file names using FTP.