How to set Wordpress Featured Images via SQL

Question

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.

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

Answer 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

Answer 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.

Answer 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