How to set WordPress Featured Images via SQL

0 Views

I am migrating this site where I have a lot of posts without featured images on them. I started doing this per page on the interface but its taking forever. I thought a using database insert might go faster.

The posts do not have an image attached to them so I can’t use the plugins that would automatically set the first image as featured.

Read More

Have any of you done this before or could point me to resources?

Post Views: 0

Related posts

Leave a Reply

3 comments

  1. I found this question interesting, and as I need to insert images in wp using directly sql I investigated some more.
    First of all you need to have a post or a page to assign the featured image to. I assume you inserted a post by way of the wp interface, or calling wp_insert_post() or directly by sql inserting into $wp->post. Then you need the ID of this post, say “target_post_id”.

    Now you need to have an image inserted in the database. Once again you can use anyone of the previous method but to be fast I load all my pre-resized images in a and insert them using sql:

    INSERT INTO $wp->post (post_type, guid, status, post_mime_type) VALUES ('attachment', '<images_url>', 'publish', 'image/jpeg')

    Last step is to bind the post to the image, as answered by cr8ivecodesmith:

    INSERT INTO $wp->postmeta (meta_value, meta_key, post_id) VALUES ('<post_id_of_image>', '_thumbnail_id', '<target_post_id>');

    there are some not null fields that wp db will fill with default values

  2. I was actually able to make it work after observing the data from the records at hand. I only had to work with the wp_postmeta table using the query:

    INSERT INTO `wp_postmeta` (meta_value, meta_key, post_id) VALUES ('<post_id_of_image>', '_thumbnail_id', <target_post_id>);

    Hope this helps other guys out there.

  3. step #1 insert a record on you wp_posts table for your attahcment

    INSERT INTO wp_posts (post_type, guid, post_status, post_mime_type,post_parent) VALUES ('attachment', '".$filename."', 'inherit', 'image/jpeg',".$parentpostid.");

    $filname = url of the image you want to use as thumbnail e.g. http://yourdomain.com/wp-content/uploads/2014/09/thumbnail.jpg

    $parentpostid = post_id of the post you want to attache your thumbnail to.

    step #2 create a post meta record on wp_postmeta for your attachement 

    INSERT INTO wp_postmeta (meta_value, meta_key, post_id) VALUES ('".$filename."', '_wp_attached_file',".$attachmentid.");

    $filename = the filename of your file. e.ge. 2014/09/thumbnail.jpg
    $attachmentid = post_id of the attachement post created on step #1

    step #3 create a post meta record to identify the attachement post as the thumbnail for the post you are attaching your thumbnail into.

    INSERT INTO wp_postmeta (meta_value, meta_key, post_id) VALUES (".$attachmentid.", '_thumbnail_id',".$parentpostid.");

    $parentpostid = the post_id of the post you want your thumbnail to be attached to.

    $attachmentid = the post_id of the attachment post created on step #1