Proper way to edit a post via phpMyAdmin without breaking the compare revisions tool

Question

In hundreds of posts on my site a specific string needs to be replaced by another one, but this replacement has only to be executed on the published post, not on the revisions.

The plugins that I found (search & replace, better search replace, etc) don't have this feature, so the job has to be done using phpMyAdmin. This can be done with this query

UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, 'tobereplaced', 'replacement')
WHERE post_type='post' AND post_status='publish'

The problem is that by doing this the most recent revision is not edited, and so in the wordpress "compare revisions" tool the edited post (containing the replacement) won't be shown.

This means that if a post is edited sequentially, say 5 times, via phpMyAdmin, then the first 4 edits will be "lost" since no revisions will be created for them.

For example

• I create the post titled abc whose content is just 1, revisions box (on wordpress) is empty
• then using the wordpress editor I replace 1 with 2, revisions box now shows two revisions: the most recent one contains 2 and the other one contains 1
• then still using the wordpress editor I replace 2 with 3, revisions box now shows three revisions: the previous two plus another one containing 3

This is the situation now

• then using the phpMyAdmin query I replace 3 with 4, no new revisions
• then still using the phpMyAdmin query I replace 4 with 5, no new revisions
• then using the wordpress editor I replace 5 with 6, revisions box now shows four revisions: the previous three plus another one containing 6

this is the situation now

So since no revisions were created for 4 and 5, the editing history of the post has a hole.

To solve this problem, I guess that when using phpMyAdmin not only the published post should be edited but also the most recent revision. In the database there is a value which is shared only by the published post and by the most recent revision, it is the post_modified date.

Is it possible to write a query which update only the published post and the most recent revision? Is this useful or there is a better workaround?

Answer 1

The reason there's a hole is because revision history isn't a history of SQL changes. Each revision is represented in the database as a post of type revision. For your changes to be shown, an additional revision post would be needed.

To do this, you'll need to update the post via PHP rather than SQL. You could do this via the interface, the REST API, calling wp_update_post, but for bulk processing I would recommend using WP CLI. Something similar to the following:

CONTENT=$(wp post get 123 --field=content)
.. adjust CONTENT to do your search replace ( maybe with sed or awk? )
wp post update 123 --post_content="$CONTENT"

https://developer.wordpress.org/cli/commands/post/update/ https://developer.wordpress.org/cli/commands/post/get/

Then just fetch every post and loop over them running the above. This will ensure all the hooks and filters get fired. It's not as quick as an SQL query but it works.

You might also want to use the search replace command instead of raw SQL to directly swap strings in the database, as it handles serialized PHP correctly in options and post meta