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?

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?

• php
• database

• Is there a specific reason you chose to use PHPMyAdmin to make this change? If you want your change to be reflected in the revision history then you can't make changes to existing data, it'll need new revision posts to be created. WP CLI would be a better approach, but PHP code is going to be necessary – Tom J Nowell ♦ Commented Sep 4, 2019 at 22:59
• I never heard about WP CLI, I went for phpmyadmin because I thought the only other way was to manually edit all the posts. So you are saying that is not possibile to edit both the published post and the most recent revision with a single query? – sound wave Commented Sep 4, 2019 at 23:06
• 2 If you want your edit to show up in the revision history, then new posts of type revision will be necessary, or maybe I've misunderstood what you're trying to achieve? Revision history isn't a timeline of SQL changes, each revision is a post – Tom J Nowell ♦ Commented Sep 4, 2019 at 23:24
• Oh yes you are right I understand now, a hole in the revision history will be made even by editing both the published post and the most recent revision with a query. So I will just ignore this problem and go on. Thank you for the kind help – sound wave Commented Sep 5, 2019 at 0:03

1 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/cli/commands/post/update/ https://developer.wordpress/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