Finally, I managed to set a featured image for all of my posts with a few queries, scripts and coffee.

Data gathering

I use WP-CLI (WordPress command line interface) for many of my posts, so if you don’t already have it, read up on it and get it installed. https://wp-cli.org/

Generate post list

Using WP-CLI “wp post list”

I grab the post list data using “wp post list” and dump the data to a volume visible to the container that I can also navigate to from my Windows desktop.

# run from wordpress installation
wp post list --post_type=post --fields=post_date,ID,post_title,post_status > /var/www/html/wp-content/uploads/archive/dump.txt --allow-root

Using MariaDB

Same data, different approach.

SELECT post_date,ID,post_title,post_status INTO OUTFILE '/repo/rawdata/hooya.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM wp_posts WHERE post_type='post' AND post_name like '$picstring%';"

Be nice to the data

Sprinkle some PowerShell to massage the data a little bit more.

# load data
$data = Import-Csv -Path "\\secretserver\w\Archive\dump.txt" -Header Date,ID,Title,Status
$posts = $data #| Where-Object Title -NotLike "YourFace*"
This is what the data in the dump file looks like…basic csv output.

Build the queries and dump to file

In a nutshell, I’m going to query for attachments that match the post date, but only select the first one found which will be used as the featured image.

# loop through posts and select the first image
$posts | ForEach-Object {
    $date = $_.Date
    $filename = $_.ID
    $title = $_.Title.substring(0,2)
    $string = $date + '-' + $title + '%'
    # generate mariadb script to get featured image id
    $query = "SELECT ID INTO OUTFILE '/repo/rawdata/$filename' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM wp_posts WHERE post_type=`"attachment`" AND post_name like `"$string`" LIMIT 1;"
    $query | Out-File "c:\temp\query.txt" -Append
}

Sample query:

SELECT ID INTO OUTFILE '/repo/rawdata/215243' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM wp_posts WHERE post_type="attachment" AND post_name like "02-05-17-Se%" LIMIT 1;

Using MariaDB, run the queries generated in the previous step.

WP Post Meta

Now I use PowerShell again to write the batch of “wp post meta add” scripts I’ll need to finish the job.

$info = Get-ChildItem -Path "\\secretserver\w\persistence\MariaDB\repo\rawdata"
$info.Count 

$posts | ForEach-Object {
    $ID = $_.ID
    $picID = $info | Where-Object Name -EQ $ID | Get-Content
    #build script
    $script = "wp post meta add $ID _thumbnail_id $picID --allow-root"
    $script | Out-File "c:\temp\booya.txt" -Append
}

The final booya text file contains all the scripts I need to set the post featured image which is run from your WordPress installation.

wp post meta add 215247 _thumbnail_id 138986 --allow-root