post meta – SQL query – get a featured image’s alt / alternative text
I am writing some SQL queries to get post data out of a database for certain posts (so WP functions are not an option). I am trying to get post IDs, post URLs, post featured image URLs and associated alt text for the featured images. Alt text is held in the postmeta table with the meta_key ‘_wp_attachment_image_alt’.
The query below works for everything except alt text. I am confident I need to use a left join because I want all posts returned regardless of whether they have featured images. My query however is returning null values for alt text for images I have confirmed have alt text.
SELECT wp_posts.id as "Post ID", wp_posts.guid as "Post URL", (SELECT wp_posts.id FROM wp_posts WHERE wp_posts.id = pm13.meta_value) AS "Featured Image ID", (SELECT wp_posts.guid FROM wp_posts WHERE wp_posts.id = pm13.meta_value) AS "Featured Image URL", (SELECT pm14.meta_value FROM wp_postmeta WHERE wp_posts.id = pm14.meta_value ) as "Featured Image Alt Text" FROM wp_posts LEFT JOIN wp_postmeta pm13 ON ( pm13.post_id = wp_posts.ID AND pm13.meta_key = '_thumbnail_id' ) LEFT JOIN wp_postmeta pm14 ON ( pm14.post_id = wp_posts.ID AND pm14.meta_key = '_wp_attachment_image_alt' ) WHERE wp_posts.post_status = "publish" AND wp_posts.post_type = "post" ORDER BY wp_posts.post_date DESC