I recently had a client ask for a one-time data dump of images in the WordPress’ Media area, including title, description, alt tag and caption.
WordPress stores common data in the POSTS table, with additional data in POSTMETA.
I used this query to export the data to CSV/Excel.
SELECT P.*,
( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND P.ID = post_id ) AS _wp_attached_file,
( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wp_attachment_metadata' AND P.ID = post_id ) AS _wp_attachment_metadata,
( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND P.ID = post_id ) AS _wp_attached_file,
( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wp_attachment_image_alt' AND P.ID = post_id ) AS _wp_attachment_image_alt
from wp_posts P where post_type = 'attachment'
ORDER BY post_date desc
Following this, we discovered the full media dump was really more than the client needed.
So, next is a similar dump returning the featured image for an entire custom post type (CPT). If you’re using something like Advanced Custom Fields (ACF), you can replace ‘_thumbnail_id‘ with any other meta key name.
SELECT ID, post_title, post_status,
(SELECT meta_value from wp_postmeta WHERE meta_key = '_thumbnail_id' AND post_id = P.ID) AS attachment_id,
( SELECT post_title FROM wp_posts WHERE id = attachment_id ) AS image_title,
( SELECT post_content FROM wp_posts WHERE id = attachment_id ) AS image_desc,
( SELECT post_excerpt FROM wp_posts WHERE id = attachment_id ) AS image_caption,
( SELECT guid FROM wp_posts WHERE id = attachment_id ) AS full_url,
( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wp_attachment_image_alt' AND attachment_id = post_id ) AS alt_tag
FROM wp__posts P
WHERE post_type = 'custom_post_type_name'
ORDER BY post_title