1

I have a Wordpress install that's using about 10,000 images. I'd like to export all filenames used in Wordpress to a file, filenames.log

filenames.log would look like this:

car.jpg boat.jpg milf.jpg car2.jpg other.jpg

etc...

The reason I'm doing this is that I have orphaned images in /uploads, and I want to run a delete operation and exclude these filenames that are actually in use.

What command could I run to generate such a file?

Thanks!

2 Answers 2

1

Pure MySQL, be sure to correct it for your desired filepath /tmp/filenames.log: (Thanks Phil for the select query)

"SELECT meta_value AS filename
FROM wp_posts p, wp_postmeta pm
WHERE p.id = pm.post_id
AND p.post_type = 'attachment'
AND meta_key = '_wp_attached_file'
INTO OUTFILE '/tmp/filenames.log'
LINES TERMINATED BY '\n';"
5
  • Thank you, and thank you Phil! I ran the above query and got this: MySQL returned an empty result set (i.e. zero rows). (Query took 0.7480 sec) Probably needs some tweak?
    – location
    Feb 22, 2015 at 21:25
  • Just checked the /tmp folder and saw that it did in fact generate the file, but the output looks like this {s:4:"file";s:72:"Diamond-ring-gold-ring.jpg";s:5:"width";s:3:"290";s:6:"height";s:3:"180";}}s:10:"image_meta";a:10:. I'd like it to output just the filenames without all the extra stuff, how could I do that? Thanks!
    – location
    Feb 23, 2015 at 20:35
  • Ok I did some digging around and I found out how to fix it: By adding AND meta_key = '_wp_attached_file'. Big thanks to you and Phil!
    – location
    Feb 23, 2015 at 20:48
  • @location ok, I've updated answer, feels good to get it figured out, doesn't it?
    – webaholik
    Feb 23, 2015 at 21:50
  • It's the best feeling ever. Exporting these names was part 1/2. Now onto part 2/2, which is deleting all images in /uploads except files matching filenames.log.
    – location
    Feb 23, 2015 at 22:29
0

Looking at the Wordpress ERD and googling a little, attachments are in the wp_posts table, as follows:

select *
from wp_posts 
where post_type = 'attachment';

Join with wp_postmeta, which is a key=>value store, on meta_key = '_wp_attached_file' to get the filename (meta_value).

So we end up with:

select meta_value as filename
from wp_posts p, wp_postmeta pm
where  p.id = pm.post_id
and    p.post_type = 'attachment';

Obviously you can filter meta_value in the WHERE clause to just return .jpgs, or whatever.

1
  • I got this when running the code: MySQL returned an empty result set (i.e. zero rows). (Query took 0.7480 sec) (I don't know if you'll be notified of my comment above. I apologize if this creates a duplicate)
    – location
    Feb 22, 2015 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.