change wordpress image directory with sql

Question

I've changed my wordpress default upload directory from:

mysite.com/files/year/month/upload
to
mysite.com/images/upload

I'm a bit stumped on the proper sql syntax to replace /files/year/month/ with /images/.

Using phpmyadmin, I selected the correct db, selected the correct table, and searched/found what needs to be changed using this sql:

SELECT * 
FROM  `wp_postmeta` 
WHERE  `meta_value` 
LIKE  '%/files/%/%/%'

Now I need to REPLACE everything FROM wp_postmeta WHERE meta_value LIKE %/files/%/%/ WITH /images/

Answer 1

To modify the entries for the uploaded media files you need to run the queries found in this article: http://www.dezzain.com/wordpress-tutorials/how-to-move-wordpress-uploads-path-to-subdomain/

Mainly these two queries:

UPDATE wp_posts SET post_content = REPLACE(post_content,'http://www.domain.com/wp-content/uploads','http://img1.domain.com/uploads')

and:

UPDATE wp_posts SET guid = REPLACE(guid,'http://www.domain.com/wp-content/uploads','http://img1.domain.com/uploads')

The problem with the other answers is that although the upload path will be fixed for new files, the source path for media that has already been inserted into posts is still pointing to the old directory since the paths are stored in the database.

Answer 2

Rather than doing it that way, WordPress provides the facility to change the upload folder using the wp-config.php file.

Basically you set WP_CONTENT_DIR for the server location and WP_CONTENT_URL for the URI's to be based on.

Answer 3

Another option is to use the following settings: update_option('uploads_use_yearmonth_folders', 0); update_option('upload_path', 'images');

Answer 4

I'd recommend the plugin WP Migrate DB for this. That's the easy way.

If your stuck on doing it the hard way, you could either run a query using REPLACE() for each path you want to replace (the function does not allow wildcards) or try something like this.