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!

    Cheers,
    Ash

    • 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'] ) ) {
        return;
      }
    
      global $wpdb;
      $sites = get_sites();
    
      foreach ( $sites as $key => $site ) {
        $site_id = isset( $site->blog_id ) ? $site->blog_id : false;
        if ( ! $site_id ) {
          continue;
        }
        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 );
        restore_current_blog();
      }
    });

    --IMPORTANT--
    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:
    http://yoursite.com/wp-admin/network/?replace_content

    Hope this helps!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.