In order to keep this simple, you will need to use a combination of SQL and some grep / awk / bash / perl type stuff.
Posts in WordPress are stored in a table called _posts, and the content is stored in a field called post_content.
So you want to be able to extract the string for all images in posts, and get the post ID as well.
When an image is inserted into the block editor using the image element in WordPress, it is interested using the wp:image tag, like this:
(17706, 3, '2023-08-19 17:11:34', '2023-08-19 17:11:34', '<!-- wp:paragraph -->\n<p>elvis Lets test this image placement</p>\n<!-- /wp:paragraph -->\n\n<!-- wp:image {\"id\":17655,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img src=\"https://www.your-domain.com/wp-content/uploads/2023/06/21366642_10159220651435332_3199626448271497166_o_10159220651435332.jpg\" alt=\"\" class=\"wp-image-17655\"/></figure>\n<!-- /wp:image -->\n\n<!-- wp:paragraph -->\n<p>elvis After image in page</p>\n<!-- /wp:paragraph -->', 'elvis Testing image', '', 'publish', 'closed', 'closed', '', 'elvis-testing-image', '', '', '2023-08-19 17:11:34', '2023-08-19 17:11:34', '', 0, 'https://www.your-domain.com/?page_id=17706', 0, 'page', '', 0),
It also uses a direct all to the image using < img src =” ….. ” >
An easy extract would be simply:
SELECT ID FROM 'wpm8_posts' where post_content like ('%img src%')
but this is not useful yet.
So lets insert the base URL so we have a link to click on, and lets get the post content as well. I would separtate these two peices of data using a pipe, but that’s just me.
Your SQL would be like this:
select 'https://www.your-domain.com/wp-admin/post.php?post=' as URL, ID, '&action=edit' as action FROM 'YOUR-PREFIX_posts' WHERE post_content like ('%img src%')
Once your sql results are complete, export the results from phpMyAdmin using the csv option, and you are left with a file with the hyperlink to all posts with an image, and the image path / name etc
There are parsers availabe in the DOMDocument so you can use just the html like this:
https://stackoverflow.com/a/10131137
https://stackoverflow.com/a/2120923
But of course you need to maintain the link to the associated post.
I would probalby use php, but it’s all what you are comforablte with – it’s a straight-forward regex:
https://stackoverflow.com/a/2120826
Your final product will be a single file with a link to your post in one column, and a fiully resolved link to the image in a second column.
The hardest part of this task will be hainvg to click on all the links to know what the image is!
Peronsally, in order to keep it all in sync, I would not delete
That’s a fun task you’ve given yourself.
And btw, I may be over thinking this, Lol. Maybe someone has already developed some sort of plugin for this but I don’t know.
There are some work arounds to use a hook that deletes all associated images when a post is deleted, but that seems pretty risky:
https://wordpress.stackexchange.com/a/282127
https://wordpress.stackexchange.com/a/109803
