SQL query to rewrite broken image URLs

Can you tell me the proper SQL search command to find the path /files/sites/ in my WordPress database? I need to replace that with /wp-content/uploads/sites/{site ID}/files. I want to search for it in my PHPMYADMIN in SQL first or do a dry run.

Please check the reported URL in the chat session and look at the broken images link and the URLs they pinpoint to. I’d like to fix them.

  • Ash
    • WordPress Hacker

    Hello Thomas

    This should be the mysql query to find those rows:

    SELECT * FROM 'wp_posts' WHERE 'post_content' LIKE '%/files/sites/%'

    Where wp_posts is the table name, it may vary based on your setup. If you use a multisite, then you have to run this for all posts table for each subsite/

    Hope it helps! Please feel free to ask more questions if you have any.

    Have a nice day!



    • Thomas
      • Site Builder, Child of Zeus

      MySQL said: Documentation

      #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_posts’ WHERE ‘post_content’ LIKE ‘%/files/sites/%’ LIMIT 0, 25′ at line 1

  • Panos
    • SLS

    Hi Thomas ,

    Of what I understand you need to add the blog_id of each blog/site on your multisite for each post that contains that string.

    Here is a sample code that you could try:

    add_action( 'load-index.php', function(){
    if ( ! isset( $_GET['replace_content'] ) ) {

    global $wpdb;
    $sites = get_sites();

    foreach ( $sites as $key => $site ) {
    $site_id = isset( $site->blog_id ) ? $site->blog_id : false;
    if ( ! $site_id ) {
    switch_to_blog( $site_id );
    $q = "UPDATE {$wpdb->prefix}posts SET post_content = REPLACE ( post_content, '/files/sites/', '/files/sites/{$site_id}') WHERE post_content LIKE '%/files/sites/%'";
    $wpdb->query( $q );


    It needs to be executed only once though, so before trying it you need to keep a backup of your db.If it is executed more than once, each time it runs it will add the blog_id one more time.

    Sorry for using the strong markup, but it is important to keep a backup of your database or at least all the posts tables (post tables for each site in your multisite) which are the ones the above query is targeting :slight_smile:

    In order to execute it, visit you network dashboard page in admin and add the “replace_content” query var in your query string:


    Hope this helps!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.