Find all Images in WordPress Table

Question

Is there a mysql query that can pull all of the images out of a table? Haven't had any luck finding a solution. Thanks

e. from a wordpress site

The images are all in the wp_posts table

In my wp_posts table all of the images are mixed in with other data. I would like to get all of the images out of this table to store on my hard drive

Solution

All records from a table

SELECT * FROM tbl

From a specific table

SELECT * FROM wp_posts

Based on Wordpress Database ERD, to get attachments, this should be close

SELECT * FROM wp_posts
WHERE post_type='attachment' and post_status='inherit'

This will give you the attachments as well as the parent post it related to, if you need some sort of context

SELECT 
  posts.ID,
  posts.post_title AS title,
  posts.post_content AS content,
  files.meta_value AS filepath
FROM
  wp_posts posts
  INNER JOIN wp_posts attachments ON posts.ID = attachments.post_parent
  INNER JOIN wp_postmeta files ON attachments.ID = files.post_id
WHERE files.meta_key = '_wp_attached_file'

If I am not mistaken, the filepath gives you a link to a disk location where the image files are actually stored. If that is all you want, just browse to it (or ftp if remote) and grab all files from there.

Comments

[BoltClock]

WordPress has an ERD? I didn't know that! +1

[Davey]

Thanks, That was very helpful!

[Allen Linatoc]

As of this writing, Wordpress 4.6-alpha-37598 to be specific, File URL is saved in guid field. Not sure though with post-based attachments/media.