Export media data directly from the WordPress database

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