Search in all posts but replace only in the published ones using phpMyAdmin

  • Resolved giannit

    (@giannit)


    4 years, 7 months ago

    Hi, I’m building my site locally, and since it has hundreds of posts, I’d like to learn how to “search and replace” without making mistakes.

    I already tried the best plugins for doing the job, but I noticed that they are just a more user friendly way to do it, they don’t add many new features with respect to phpMyAdmin.

    So I’m learning how to do it manually via phpMyAdmin, and it seems very easy too.
    In particular, I have to search for a specific string in all posts, and replace it with another string, but only in the published version of the posts, without touching the revisions.

    First thing I tried was the phpMyAdmin “Search and replace” built-in tool, but I found out that it replaces the text both in the published posts and in the revisions.
    Moreover, I noticed that the query used by the tool is in the form

    UPDATE 'wp_posts' SET 'post_content' = REPLACE('post_content', 'text to replace', 'replacement text') WHERE 'post_content' LIKE '%text to replace%' COLLATE utf8mb4_bin

    Then, looking at the columns of the db, I found the post_type and post_status columns, which can be useful to achieve what I want.
    So in the SQL tab I run the following query

    UPDATE 'wp_posts' SET 'post_content' = REPLACE('post_content', 'text to replace', 'replacement text') WHERE post_type='posts' AND post_status='publish'

    which worked as expected.
    But now I have a doubt, looking at the rows of the db, I found out that for a given post there is a revision “linked” to the published post. What I mean is that if we create a post and then we edit it, there will be not one but two revisions lines in the db (plus the line of the published post, so 3 lines in total), the first one is the copy of the first version of the post, and the second one is the copy of the second version of the post and it is equal to the published post.
    By running the second query, the two revisions are left untouched, while the published post is edited, but I feel like also the second revision should be edited, otherwise the published post won’t be linked anymore to any revision.

    So my first question is, are there consequences to this? That is, is it bad to edit the published post and not to edit its linked revision?

    The second and last question is about the two queries I posted (first one generated by phpMyAdim, second one written by me), is it ok not to write this part?

    WHERE 'post_content' LIKE '%text to replace%' COLLATE utf8mb4_bin

    I dint’t write it in the second query because I don’t see the point of the command
    WHERE 'post_content' LIKE '%text to replace%'
    since the first part
    UPDATE 'wp_posts' SET 'post_content' = REPLACE('post_content', 'text to replace', 'replacement text')
    already tells to search for the string ‘text to replace’ in the ‘post_content’, doesn’t it?

    p.s. yes I have a backup of the db

    • This topic was modified 4 years, 7 months ago by giannit.
    • This topic was modified 4 years, 7 months ago by giannit.
    • This topic was modified 4 years, 7 months ago by giannit.
    • This topic was modified 4 years, 7 months ago by giannit.
    • This topic was modified 4 years, 7 months ago by giannit.
    • This topic was modified 4 years, 7 months ago by giannit.
Viewing 3 replies - 1 through 3 (of 3 total)
  • Joy

    (@joyously)

    4 years, 7 months ago

    So my first question is, are there consequences to this?

    The revisions are like a backup, a history of edits. If you want the text replaced everywhere, even if it changes your history, go for it. The link between the revision and the published post is in the parent field, not the content.

    is it ok not to write this part?

    You should put whatever you need in the WHERE clause to accurately select the rows you want to affect. There are performance ramifications, but on a test database that doesn’t matter. The collation could affect the order that the UPDATE happens, if there are expressions that sort or affect a sort in the rest of the command. In your case, finding the rows with the match ensures that only those rows are manipulated.

    Thread Starter giannit

    (@giannit)

    4 years, 7 months ago

    @joyously Thank you for your kind answer and clarification.

    If you want the text replaced everywhere, even if it changes your history, go for it.

    I’d like to replace text not everywhere but only in the published post (and maybe in its last revision).
    The problem in modifying only the published post is, I think, that when I go to compare the revisions (in order to restore an old one), wordpress will show the comparison between the last revision (which is different from the published post) and the previous revision. So it won’t be possible to compare the published post with the second last revision.
    Is this what will happen?

    Joy

    (@joyously)

    4 years, 7 months ago

    I haven’t looked at how the revision compares work. I know I have seen a message saying that there is a more recent autosave (like if you make a change, but get out of the editor without saving).

    You might have to sort your revisions by date to change only the last one. I don’t know how to do that in an UPDATE.
    But changing the revision is changing the history you had. And this change, directly through the database, doesn’t produce a new revision for you.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Search in all posts but replace only in the published ones using phpMyAdmin’ is closed to new replies.