How to Export Image Filenames from WordPress Database

exportMySQL

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!

Best Answer

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';"

Related Solutions

Sql-server – SQL Server 2000 export database to csv

There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).

Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.

While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.

Export Image Column to Files in SQL Server – Step-by-Step Guide

This is the solution I came up with:

Enable xp_cmdshell with

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

If needed create a directory with xp_cmdshell to get the needed permissions.
```
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
```

Use BCP with queryout

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]

Best Answer

Related Solutions

Sql-server – SQL Server 2000 export database to csv

Export Image Column to Files in SQL Server – Step-by-Step Guide

Related Question