city search

Code Samples,Database

Global Find and Replace In WordPress using MySQL

4 Feb , 2009  

Wordpress LogoIn case you ever need to find and replace a text string in every post or page of your WordPress site, this SQL code will help you out. I recently had to move a client’s website from staging into production, and just when I was getting ready to publish the new site I realized WordPress’s HTML editor had hard-coded the URLs of every image in every post to the address of the staging server.  This is actually a feature of WordPress; when you upload an image into a post or page it uses an absolute URL, not a relative one.  This really helps you out if any of your posts get picked up via RSS and published elsewhere, since the images will remain intact thanks to the absolute URLs.

NOTE: An absolute URL is the fully qualified path to a resource; such as http://www.barrywise.com/images/image.jpg.  A relative URL will only work when the file is relative to the path of the current page, such as ../images/image.jpg.

So here I was with a couple hundred pages of content with image URLs all pointing to the wrong address on the staging server.  I needed a quick way to do a find and replace on every post in the WordPress MySQL database.  It was actually pretty simple.  In my case, I just had to execute this SQL code against the MySQL WordPress database:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'staging.server.com', 'www.productionserver.com');

This code will search through every “post_content” field in the wp_posts table and replace the staging URL address (staging.server.com) with the production address (www.productionserver.com).  The format is easy to understand and can of course be used with any MySQL table, not just WordPress:

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

I realize this isn’t the most complex or enlightening code sample, but I hope this helps anyone stuck with an annoying repetitive text string in all your WordPress posts which you’d like to search and replace with one quick command.

, , , , ,


36 Responses

  1. I hope I never have to experience that. I forgot that WordPress uses the absolute URL for images. Luckily I’ve never had a huge development site on WordPress. Thanks for the tip, I’ll but it in my little knowledge vault.

    Casey

  2. I’m just coming to the end of developing a pretty image-heavy WordPress site on a test server – I hadn’t even thought about the absolute image URLs!

    But like you say, it’s a fairly simple fix. Thanks for sharing

  3. Bryan Phelps says:

    Or you can use the WP plugin: http://wordpress.org/extend/plugins/search-and-replace/

    For those of us who tend to only break things on MYSQL…

  4. Barry Wise says:

    Thanks, Bryan, I didn’t know there was a plugin for that.

  5. Web Talk says:

    The only thing i can think of as fr as mysql is concerned is that it is a tricky place to explore! I hope not to get this kind of issue at all. in that case i will call you! you are forewarned! LOL!

  6. Good post; a handy reference or reminder. One could argue that WordPress should use something like {siteUrl} in its DB content, and then run the various DB columns through a macro parser to make things like this a bit more transparent. This could even be done at the database query level.

    Thanks for the post nevertheless!

  7. Evan says:

    I just changed the the location of the images in my blog by unchecking “Organize my uploads into month- and year-based folders” in Settings_Miscellaneous. This simple bit was exactly what I needed to update image urls in my existing posts. It took me A LOT of searching to find it, so maybe by someone else will pick up this comment in the future. Thanks a ton!

    Evan

  8. Gavin says:

    Thanks for the article, I’ve been looking for a simple way of updating multiple hard coded image URLs for some time and this does the job perfectly.

    The plugin Bryan mentions looks like a good bet for anyone who doesn’t want to get their hands dirty with the SQL though!

  9. Tom Holder says:

    Thanks, this is great. I’m using onepress community for a blog and for some reason that puts images in as attachments in to the wp_posts table and needs the above doing to the ‘guid’ column!

  10. I think if you keep the same paths and the same name of folders, the moving should not cause any problems. However, if you do need to change something, then this could be a pain, I agree.

    Thanks for sharing your method.

    Tony

  11. deb says:

    anyone know how to find and replace on “pages”? not just posts…
    thanks!
    deb

  12. Dave Daniels says:

    Thank you, this was a HUGE help. I changed my domain name and wanted to change the copyright notice on every post, and this did the trick in seconds instead of HOURS of manually editing each post. THANK YOU!

  13. I’ve had to do this in the past…I just exported the database and did find and replace in text mate… but this sounds a lot easier

  14. Just wanted to say thanks! I was searching all over for a fix like this. Didn’t even know where to run the script so i added a mini tutorial for dummies like myself below.

    Cheers,
    Heyman

    How to run/execute sql script
    -Login phpMyAdmin
    -Click Databases
    -Click the database WordPress is using
    -Click the SQL tab
    use the code, hit go

  15. ovidiu says:

    how would I search i.e. mydomain.com and replace with mydomain.org throughout a whole database, not just in posts/pages?

  16. ITCN says:

    @ovidiu You can’t do a whole database search and replace at once (at least not with 1 SQL statement); you have to do it table by table, but you can use the same query I noted above.

  17. irms says:

    @Ovidiu I wrote a quick script you can upload to your server and do a find and replace across an entire database. Use at your own risk! There are no real safeguards in this script. One string simply replaces another string.

    http://blog.irmsgeekwork.com/2009/phpmysql-full-database-search-and-replace

  18. ovidiu says:

    thx, will check it out

  19. Neil says:

    I love the internet, cause smart people like you can make clever people like me – POWERFUL!!

    cheers to having the answer when I needed it!!

  20. ADA sport says:

    This string is exactly what I have been looking Thanks …

  21. @David, that is a good idea for the global search and replace. My question is, does the mysql file keeps its structure?

    for example, I download and open up in word. Do the search and replace and save. Upload to the server? Is that correct? The file structure should remain the same??

    Thanks in advance

  22. I’ve written a plugin at WordPress.org which enables you (with a GUI) to find and replace in your posts,pages and/or trashed items: http://wordpress.org/extend/plugins/find-replace/

  23. Thanks Barry! I just used this and it worked like a charm!

  24. Mark Fregnan says:

    Thank you very much Barry.

    Your post saved me hours of work. We wanted to change the HTML code paragraph formatting on our posts and pages and your SQL query did the trick.

    I also used the WHERE statement…

    -Logged into phpMyAdmin via my Host Cpanel
    -Selected the WordPress MYSQL database from the list of databases
    -Clicked on table wp_posts
    -Clicked the SQL tab
    -Cut and pasted the code below, and clicked [Go]

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘OLD HTML’, ‘NEW HTML’) WHERE post_type = ‘post';

  25. Jim says:

    You probably want to also do the search and replace on the wp_postmeta table.

  26. Shovan says:

    Yay! It works, But my WP-Admin still redirects to old site.. any ideas?

  27. Leland Gold says:

    Thanks a lot barry man! I use this so much now, I’m not sure what I would do without it. Just got into this myself, but thanks again for the help!

  28. Mary says:

    Awesome! I just started learning MySQL so this was magical. I just migrated a client site from godaddy’s “preview dns” to the live site – without realizing the images wouldn’t migrate. Saved me tons of time and work, and taught me something new – thanks!

  29. Don says:

    Hello, what I’m trying to do is globally replace all urls to a specific domain which is the the content of many posts but since each url is different only the domain name remains the same I’m having a problem. I figured smthg like:

    UPDATE wp_posts SET post_content = REPLACE(post_content, ‘www.domain.com/%’, ‘www.new.com’);

    But how do I limit it to just the url and not replace everything including content after the url?

  30. Tom says:

    It’s also likely necessary to run this:

    UPDATE wp_posts SET guid = REPLACE(guid, ‘old.com’, ‘new.com’);

    to update any image attachment URLs.

  31. Chris Pink says:

    It’s almost right (not sure why nobody has picked up on this, maybe it’s down to a phpmyadmin version change).

    you need to enclose ‘post_content’ in single quotes thus;

    UPDATE wp_posts SET post_content = REPLACE(‘post_content’, ‘staging.server.com’, ‘www.productionserver.com’);

  32. Barry Wise says:

    Hi Chris;
    I think that’s just your phpmyadmin. I use it without single quotes without a problem

  33. Steve says:

    Ive got a huge task of updating 4000 pages that contain a nextgen gallery, and wandered if anyone could help.

    I actually need to change the HTML of every page that uses a specific page template

    I need to look for the following, however I need to be able to put in a global character search as every page uses a different Nextgen Gallery ID Number.

    [nggallery id=XXXX]

    And replace it with
    [one_half]
    [nggallery id=XXXX]
    [/one_half] [one_half_last]
    Release Details

    Record No
    Artist
    Title
    Album Single
    Format
    Catalogue Number
    Record Label
    Release Date
    Country
    Promotional Item
    Notes

    Tracks:
     
     
    Song Credits:

    Production Credits:
     

    Other Credits:
     
    [/one_half_last]
     
    [one_half]
     

    Can anyone suggest whether this is possible in anyway, it could save me a significan amount of time!!

    Many thanks

    Steve

  34. rozina says:

    Never enclose ‘post_content’ in single quotes. You will lose all your posts – The command will wipe out all your post content and replace with just the new url name.

  35. Website Development says:

    This code really works and we find and replace the code using Update SQL string.

    Thanks

Comments are closed.