Change upload URL by mime type

Question

I'm working on a site that has several hundred pdf files uploaded over the course of a few months. All uploads are organised in the uploads directory by year/month.

The client has just decided they don't want dates in their pdf urls. Instead of having the URL site.com/app/uploads/2019/06/whatever.pdf they want the url site.com/app/uploads/pdf/whatever.pdf. Note that this only applies for pdf files.

For new uploads this is easy enough. I added a filter for wp_handle_upload_prefilter that checks if the extension is pdf and changes the upload directory to uploads/pdf accordingly. However I'm not sure how to change the URL for all existing pdfs.

I looked into moving all pdfs into the uploads/pdf directory and then modifying the wp_posts GUID field to reflect the new location with

UPDATE
  `wp_posts`
SET
  guid = CONCAT(
    SUBSTRING_INDEX(guid, '/uploads', 1),
    '/uploads/pdf/',
    SUBSTRING_INDEX(guid, '/', -1)
  )
WHERE
  post_mime_type = 'application/pdf'

..but this didn't work. (done on a test site). The GUIDs were changed but the URLs remained the same.

How can I update the URLs of all pdf uploads without modifying URLs of any other upload type?

Answer 1

It turns out the value that points to the PDFs' locations on disk is stored in the wp_postmeta table, under the meta_value field.

For example a media item located at site.com/app/uploads/2019/06/whatever.pdf would have a meta_value value of 2019/06/whatever.pdf. To change the location of all PDFs I first found the IDs of all PDFs (from the ID column in the wp_posts table, selecting where post_mime_type='application/pdf) and then, for each of those IDs, found the corresponding row in the wp_postmeta table using the post_id column and updated that row's meta_value field, replacing the year/month/ with pdf/.

I used a PHP script:

$ids = $wpdb->get_results( "SELECT * FROM `wp_posts` WHERE post_mime_type='application/pdf'" );
foreach ( $ids as $id ) {
    $pdf = $wpdb->get_results( "SELECT * FROM `wp_postmeta` WHERE post_id=$id->ID" );
    $oldpdf = $pdf[0]->meta_value;

    $_tmp = explode("/",  $pdf[0]->meta_value);
    if ( sizeof($_tmp) > 2 ) {
        unset( $_tmp[0], $_tmp[1] );
        $newpdf = 'pdf/'.implode("/", $_tmp);
    }
    else {
      $newpdf = $oldpdf;
    }

    if ( $oldpdf != $newpdf ) {
        rename( wp_get_upload_dir()['basedir'].'/'.$oldpdf, wp_get_upload_dir()['basedir'].'/'.$newpdf );
        $wpdb->update(
            'wp_postmeta',
            [ 'meta_value' => $newpdf ],
            [ 'post_id' => $id->ID ]
        );
    }
}