I currently have a site that has a number of custom post types. I want to ideally have the following structure /wp-content-uploads/posttype/postid/xxxx.jpg. All the images are attachments of the posts themselves

I have a script to move the current images by using the following MySQL

SELECT p.id, p.post_type, p.guid, p.post_parent, m.meta_id, m.meta_key, m.meta_value, p2.post_type as parent_type
FROM wp_posts AS p
INNER JOIN wp_postmeta AS f ON f.post_id = p.id AND f.meta_key = '_wp_attached_file'
INNER JOIN wp_postmeta AS m ON m.post_id = p.id AND m.meta_key = '_wp_attachment_metadata'
INNER JOIN wp_posts AS p2 ON p2.id = p.post_parent
WHERE p.post_type = 'attachment'
AND p2.post_type IN('customposttype1','customposttype2','customposttype3')

With this I get all the relevant info in order to create the various folders and move the images, so this side of things is not an issue.

The issue I have is whilst I run the script and the images are moved, the post still references the old images ie wp-content/uploads/xxx.jpg.

Once I have moved the images I am updating the images guid in the wp_posts table, I figured this would be where the image location is stored, but now I am guessing is that wordpress uses a fixed uploads structure depending on what is selected in the backend rather than storing the location in the database.

Can someone confirm if this is the case or what table stores the actual path to the image that I could change in my script for the images to appear as expected.

I have backed up the database and looked at the file and I see the images referenced in a few places, but any full paths appear in the format that I have supplied.

I currently have a site that has a number of custom post types. I want to ideally have the following structure /wp-content-uploads/posttype/postid/xxxx.jpg. All the images are attachments of the posts themselves

I have a script to move the current images by using the following MySQL

SELECT p.id, p.post_type, p.guid, p.post_parent, m.meta_id, m.meta_key, m.meta_value, p2.post_type as parent_type
FROM wp_posts AS p
INNER JOIN wp_postmeta AS f ON f.post_id = p.id AND f.meta_key = '_wp_attached_file'
INNER JOIN wp_postmeta AS m ON m.post_id = p.id AND m.meta_key = '_wp_attachment_metadata'
INNER JOIN wp_posts AS p2 ON p2.id = p.post_parent
WHERE p.post_type = 'attachment'
AND p2.post_type IN('customposttype1','customposttype2','customposttype3')

With this I get all the relevant info in order to create the various folders and move the images, so this side of things is not an issue.

The issue I have is whilst I run the script and the images are moved, the post still references the old images ie wp-content/uploads/xxx.jpg.

Once I have moved the images I am updating the images guid in the wp_posts table, I figured this would be where the image location is stored, but now I am guessing is that wordpress uses a fixed uploads structure depending on what is selected in the backend rather than storing the location in the database.

Can someone confirm if this is the case or what table stores the actual path to the image that I could change in my script for the images to appear as expected.

I have backed up the database and looked at the file and I see the images referenced in a few places, but any full paths appear in the format that I have supplied.

• images
• uploads
• media-library

1 Answer 1

The GUID is not the image's URL. It typically does end up (coincidentally) being the original URL of the image, because it needs to be a unique identifier - but that's all WordPress uses it for, as a unique identifier.

Your code should successfully update the URLs of the featured images of posts, but only the featured images. It sounds like you are probably running into references to the images inside the post_content itself (as in, in the (prefix)_posts table, in the post_content column of each individual post and revision). This happens both with the Classic Editor and the Block Editor - the full image paths are included in the <img> tag.

So, you'll have to update all of the Posts/Pages/CPTs that include images. This will probably be easiest in PHP, where you can get all posts, loop through them with a regular expression, and replace the image paths programmatically. (If you're running a smaller site with just a little old content, it might be faster to do it by hand.)

But, there's one more place in the database that references the location of the image - the URL that you see in the Media Library, so WordPress knows when you add the image to a new post it will add the right path. That's in the (prefix)_postmeta table. For each individual image file, you'll find one entry in (prefix)_posts where you can grab the IDs. Then, for each of those IDs, there will be two keys in the (prefix)_postmeta table that refer to the path. The "_wp_attached_file" postmeta stores the path and file name as a simple string. More complicated to deal with is the other one, "_wp_attachment_metadata". This stores the path and file name inside a serialized array - meaning you can't just search and replace strings, or it will break the serialization. So, it will take more doing to find a way to update that postmeta value in a way that causes WordPress to look for the image in the right place.

Really, if your image library is small, you may want to even consider uploading all of the media freshly through the Media Library into their new location, and then going back and manually updating posts (though of course if your site is larger, this will be rather time-consuming, so you might want to figure out the serialization instead and do things programmatically.)