Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm helping a friend with a WordPress blog since I'm a "computer person," although I have no specific WordPress experience and I haven't used PHP in years. So, I really appreciate any light that anyone can shed on this subject.

The primary purpose of the blog is to catalog items and there are about 8,000 posts, each containing one photo. The blog began on another platform and was migrated to WordPress years ago, so things are pretty mucked up on the back end. Thousands of the posts were migrated from the other platform, and then thousands more were posted from the WP CMS. They recently upgraded to the latest version of WordPress (and a new theme, Themolio) and now are having two major problems (with the first being the biggest problem, though I'd like to solve them both):

  1. The <img> tag for the photo in each post is contained in the post_content field. However, some of the URLs are absolute, and some are relative based on the messy history of the blog I described above. I do not know which are "correct," i.e., the way WP generates them, and which were imported from the other platform. The absolute ones are appearing fine everywhere. The relative ones are fine within each post, but are breaking when they appear within the Archive pages. Can I somehow get the images to appear correctly on all pages despite the fact that each page may have a different path? Should they all be relative or all absolute? And finally, can I use some combination of MySQL queries and regular expressions to update all the entries in the DB to be uniform?
  2. No thumbnail images are appearing on the front page of the blog or on the Search page. There are not even broken links- there are no links at all. It seems that Featured Images in WP are supposed to have a meta_key field called _thumbnail_id... none of these posts have this. It appears the old theme on the old version of WP was pulling thumbs from a meta_key field called "Featured Image", which contains the filename of the image relative to the /uploads/ folder. Clearly this is going to be an issue... can I somehow get WP to get thumbnail URLs from this Featured Image meta_key?

Thank you!!!

Solution
Based on the comments from Gorit, here is the code I used to fix each problem:

Question 1: Executed a SQL command pretty much exactly as written in her post. Change whatever details you need to for your particular case.

Question 2: Ran this piece of php code placed in the root folder.

require_once './wp-config.php';
global $wpdb; 
$wpdb->show_errors();
$posts = $wpdb->get_results("SELECT id, post_parent FROM wp_posts WHERE post_type = 'attachment'", ARRAY_N);
if ($posts) {
    foreach ($posts as $post) {
    // First insert a thumbnail_id meta tag for the attachment that points to itself
    $wpdb->query("INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES ('" . $post[0] . "', '_thumbnail_id', '" . $post[0] .  "')");
    // Next, insert a thumbnail_id meta tag for the attachment's parent post that points to the attachment
    $wpdb->query("INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES ('" . $post[1] . "', '_thumbnail_id', '" . $post[0] .  "')");
    }
}
share|improve this question

1 Answer 1

up vote 0 down vote accepted

All your issues could be solved with some MySQL queries.

Regarding point #1: if you are using mod_rewrite then I recommend using absolute URLs for images in posts, if you are displaying full post content (including its images) on other pages - as you suggest.

You can do a MySQL search/replace as follows:

UPDATE wp_posts SET post_content = replace(post_content, 'old string', 'new string');

So, for example, if all your images are located in the folder wp-content/uploads (that's where WordPress usually places the media), then your search/replace could be something like:

UPDATE wp_posts SET post_content = replace(post_content, 'src="uploads/', 'src="http://www.example.com/uploads/');

Regarding point #2:

You're going to need to grab the ID of the image from wp_posts table, in order to add it to the wp_postsmeta table.

Let's say you have in wp_postsmeta some meta data for post_id 1. One of these is 'featured image', so the row would be something like:

post_id = 1; meta_key = 'featured_image'; meta_value = 'uploads/2012/12/someimage.jpg';

to get the ID of that image, you'd do:

SELECT ID FROM wp_posts WHERE guid LIKE '%uploads/2012/12/someimage.jpg';

You'd then be able to insert to wp_postsmeta the data as:

INSERT INTO wp_postsmeta (post_id, meta_key, meta_value) VALUES (1, '_thumbnail_id', ID-OF-IMAGE);

So, to combine it all, use a simple php script as follows:

    $posts = $wpdb->get_results("SELECT post_id, meta_value FROM wp_postmeta WHERE meta_key='featured_image'", ARRAY_N);
    if ($posts) {
        while ($posts as $post) {
            $image = $wpdb->get_row("SELECT ID FROM wp_posts WHERE guid LIKE '%" . $post[1] . "%'", ARRAY_N);
            if ($image_id) {
                $wpdb->query("INSERT INTO wp_postsmeta (post_id, meta_key, meta_value) 
                  VALUES ($post[0], '_thumbnail_id', '" . $image[0] . "')");
            }
        }
    }

Hope all of this gives you some direction and assistance. I've written it in broad terms, so please remember to back up your db before doing any changes!

Good luck.

share|improve this answer
    
Thanks for your help. I just got around to implementing this yesterday and today. Your solution to part one was pretty much plug-and-play. For part two, I altered your php script because of some issues with the guids in the DB, but your code was still very helpful to work from. In case anyone needs to do this in the future, I posted my final code above. –  Andy Feb 8 '13 at 1:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.