Images import

Matching on first related images. Apologies to the original Content Creator. Maybe the match should be on the last image ...

[eckfordc@s12751 octopus.eckford.ca]$   wp db query "
 SELECT ID,
 REGEXP_SUBSTR(post_content, '<img[^>]+src=\"[^\"]+\"') AS img
 FROM wp1r_posts
 WHERE ID IN (103, 577, 589, 842, 2369);"
ID    img
103    
577    <img class="size-medium wp-image-4150" src="https://octopus.eckford.ca/wp-content/uploads/2013/02/Bon_Jovi_1-300x200.jpg"
589    <img class="alignnone size-full wp-image-4125" src="https://octopus.eckford.ca/wp-content/uploads/2013/03/Unknown-2.jpeg"
842    <img class="size-medium wp-image-3345" src="https://octopus.eckford.ca/wp-content/uploads/2016/02/Paul_McCartney_Dublin_2010-300x287.jpg"
2369    <img class="size-medium wp-image-5051" src="https://octopus.eckford.ca/wp-content/uploads/1998/07/Bon-Jovi-300x216.jpeg"

export post_id and wp-image ID pairs from the database using a wp db query, then process them in batches of 50. The user will run the export command, save to a file, and then run a batch processing script that reads 50 lines at a time for applying changes.

 1) Generate the full list (post_id → wp-image-####):

  wp db query "
  SELECT p.ID,
    REGEXP_SUBSTR(p.post_content, 'wp-image-[0-9]+') AS wp_image
  FROM wp1r_posts p
  WHERE p.post_type = 'post'
    AND p.post_status = 'publish'
    AND p.post_content LIKE '%wp-image-%';" \
  --skip-column-names > /home/eckfordc/octopus.eckford.ca/post_image_list.tsv

  2) Create a 50‑row batch from that list:

  head -n 50 /home/eckfordc/octopus.eckford.ca/post_image_list.tsv > /home/eckfordc/octopus.eckford.ca/
  featured_batch.tsv

  3) Apply the batch:

  while IFS=$'\t' read -r post_id wp_image; do
    att_id=${wp_image#wp-image-}
    wp post meta update "$post_id" _thumbnail_id "$att_id"
  done < /home/eckfordc/octopus.eckford.ca/featured_batch.tsv

  When you’re ready for the next 50:

  tail -n +51 /home/eckfordc/octopus.eckford.ca/post_image_list.tsv | head -n 50 > /home/eckfordc/octopus.eckford.ca/
  featured_batch.tsv

  Run the same apply loop again.

  head -n 5 /home/eckfordc/octopus.eckford.ca/featured_batch.tsv |  awk -F'\t' '{print $1}' |  paste -sd, - > /tmp/batch_ids.txt
 

[eckfordc@s12751 octopus.eckford.ca]$   head -n 5 /home/eckfordc/octopus.eckford.ca/featured_batch.tsv | \
  awk -F'\t' '{print $1}' | \
  paste -sd, - > /tmp/batch_ids.txt
[eckfordc@s12751 octopus.eckford.ca]$  IDS=$(cat /tmp/batch_ids.txt)
[eckfordc@s12751 octopus.eckford.ca]$   wp db query "
  SELECT ID,
    REGEXP_SUBSTR(post_content, '<img[^>]+src=\"[^\"]+\"') AS img
  FROM wp1r_posts
  WHERE ID IN ($IDS);" --skip-column-names
294	<img class="size-medium wp-image-4818" src="https://octopus.eckford.ca/wp-content/uploads/2011/10/GImyYVZ0_400x400-300x192.jpg"
299	<img class="size-medium wp-image-4692" src="https://octopus.eckford.ca/wp-content/uploads/2011/09/1172569-foster-the-people-portrait-lollapalooza-2011-617-509-300x199.jpg"
304	<img class="size-medium wp-image-2046" src="https://octopus.eckford.ca/wp-content/uploads/2018/01/Matt-Andersen_by-Colin-Robertson-200x300.jpg"
308	<img class="size-medium wp-image-5130" src="https://octopus.eckford.ca/wp-content/uploads/2011/10/Kings_of_Leon_Live_in_Concert_May_2017-300x300.jpg"
316	<img class="size-medium wp-image-5115" src="https://octopus.eckford.ca/wp-content/uploads/2011/10/awolnation_hollow_moon-592-300x200.jpg"

• Here are the 5 URLs from the batch output:

 - https://octopus.eckford.ca/wp-content/uploads/2011/10/GImyYVZ0_400x400-3...
 - https://octopus.eckford.ca/wp-content/uploads/2011/09/1172569-foster-the...
   509-300x199.jpg
 - https://octopus.eckford.ca/wp-content/uploads/2018/01/Matt-Andersen_by-C...
 - https://octopus.eckford.ca/wp-content/uploads/2011/10/Kings_of_Leon_Live...
 - https://octopus.eckford.ca/wp-content/uploads/2011/10/awolnation_hollow_...
 

I would like to run the last of these automated updates please. Should we flag failures for a later
 review.

• I'm preparing to provide bash commands that list attachment IDs with missing files based on the _wp_attached_file
 meta field, outputting batches and failures in TSV files to enable easy flagging of missing featured images.

• Yes — let’s run the full update and log anything missing for later review.

 1) Build the full list and apply featured images (posts only, no existing _thumbnail_id):

 wp db query "
 SELECT p.ID,
   REGEXP_SUBSTR(p.post_content, 'wp-image-[0-9]+') AS wp_image
 FROM wp1r_posts p
 LEFT JOIN wp1r_postmeta pm
   ON pm.post_id = p.ID AND pm.meta_key = '_thumbnail_id'
 WHERE p.post_type = 'post'
   AND p.post_status = 'publish'
   AND pm.post_id IS NULL
   AND p.post_content LIKE '%wp-image-%';" \
 --skip-column-names > /home/eckfordc/octopus.eckford.ca/featured_batch.tsv

 while IFS=$'\t' read -r post_id wp_image; do
   att_id=${wp_image#wp-image-}
   wp post meta update "$post_id" _thumbnail_id "$att_id" >/dev/null
 done < /home/eckfordc/octopus.eckford.ca/featured_batch.tsv

 2) Flag missing files for later review:

 > /home/eckfordc/octopus.eckford.ca/featured_missing.tsv
 while IFS=$'\t' read -r post_id wp_image; do
   att_id=${wp_image#wp-image-}
   path=$(wp db query "
     SELECT pm.meta_value
     FROM wp1r_postmeta pm
     WHERE pm.post_id = $att_id AND pm.meta_key = '_wp_attached_file'
   " --skip-column-names)
   [ -z "$path" ] && echo -e "$post_id\t$att_id\t(no _wp_attached_file)" >> /home/eckfordc/octopus.eckford.ca/
 featured_missing.tsv && continue
   file="/home/eckfordc/octopus.eckford.ca/wp-content/uploads/$path"
   [ ! -f "$file" ] && echo -e "$post_id\t$att_id\t$path" >> /home/eckfordc/octopus.eckford.ca/featured_missing.tsv
 done < /home/eckfordc/octopus.eckford.ca/featured_batch.tsv

 That leaves you with:

 - featured_batch.tsv (what was updated)
 - featured_missing.tsv (what needs attention later)

 If you want, I can add counts or a quick summary at the end.