SQL Query Updated Revisions, Not Published Posts. How to Fix?

Resolved tubella
(@tubella)

3 years, 5 months ago
Hello!

I’d appreciate help with the following.

I used an SQL query to introduce a mass update of html tags in multiple posts:

UPDATE wp_posts SET post_content = REPLACE (post_content, '<blockquote>\n<h5>', '<blockquote class="warning">');

(where ‘\n’ is a regular expression, since two tags were on separate lines.)

As a result, all changes were made to last saved revisions of posts, not to published posts.

Moreover, now if I run

UPDATE wp_posts SET post_content = REPLACE (post_content, '<blockquote>\n<h5>', '<blockquote class="warning">') WHERE post_status = 'publish';

the query returns zero results, showing 0 published posts with <blockquote><h5> but in reality there are lots of published posts having them.

Question:
What is the correct SQL query to run mass update?

Thank you
- This topic was modified 3 years, 5 months ago by tubella.

Viewing 10 replies - 1 through 10 (of 10 total)

Dion
(@diondesigns)

3 years, 5 months ago

Without seeing the actual post content stored in the database, it’s hard to help you debug this. Two comments:

1) If the updated posts are edited, the changes will likely disappear.

2) Are you aware that your UPDATE query will remove a <h5> tag?

Howdy_McGee
(@howdy_mcgee)

3 years, 5 months ago

If you’ve already ran the first SQL query, you’ve already updated all post content, published or not, to be a block quote without an h5 tag. Your second query will not return results if you’ve already ran the first SQL query because that combination no longer exists.

Joy
(@joyously)

3 years, 5 months ago

You could try using the plugin Search Regex, which lets you see what the change will do before you actually do it.
https://wordpress.org/plugins/search-regex/
Thread Starter tubella
(@tubella)

3 years, 5 months ago
Dion Designs (@diondesigns),

thank you for your comments!

1) When I open any published post in HTML editor, I see all <blockqoute><h5>in place. They didn’t disappear after SQL run.

2) Yes, sure I am aware of a <h5> tag removal. For this reason I ran UPDATE wp_posts SET post_content = REPLACE (post_content, '</h5>\n</blockquote>', '</blockquote>'); along with the above-mentioned query. I just omitted this part in my question to simplify it.

The trick is that even with the search tool in phpMyAdmin I cannot find any <blockqoute><h5> as if the database doesn’t have it. But they sure are there!

If you can help me, perhaps, somehow I can provide you the database?

Howdy_McGee (@howdy_mcgee)

Thank you for your input!

If you’ve already ran the first SQL query, you’ve already updated all post content, published or not, to be a block quote without an h5 tag. Your second query will not return results if you’ve already ran the first SQL query because that combination no longer exists.

No, that is not the case. The published content is not updated. That’s the reason of my question.

The most frustrating part that even after the first SQL query the combination does exist. Because I see it in all published posts in WordPress HTML editor.

Joy (@joyously)

Thank you for advice!

Yes, I’ve tried Search Regex.
But personally, I found it a bit limited.
I see more search, browse, locate, and filter options with phpMyAdmin.

As a result: I am still struggling to update published posts. It is a puzzle )

Please, I’d be thankful for any ideas!

Thank you
- This reply was modified 3 years, 5 months ago by tubella.
Joy
(@joyously)

3 years, 5 months ago

Well, you’re not doing regex with your SQL query, whereas you could with the plugin. And the plugin shows it before you actually make the change, so it’s safer. And you’re having problems with published vs. unpublished, which doesn’t happen with the plugin.
The plugin also has a link to each post that would be changed, so you can see it in the context of WP and not the database.

Perhaps if your posts have blocks, then you won’t find the actual HTML in the posts table.

Moderator bcworkz
(@bcworkz)

3 years, 5 months ago

The fact there is a line feed within your search string can make it difficult to exactly match the white space. There could be extra space chars, tab chars, return chars, etc., in any possible order. This variability is where regex becomes very powerful. All possible combinations of whitespace can all be represented with one regexp token.

If you still cannot find occurrences of blockquote tags in the DB with regexp, it’s likely the tags are not in the DB at all, but are inserted by template code or the block editor’s parser like Joy suggested.

Thread Starter tubella
(@tubella)

3 years, 5 months ago

f you still cannot find occurrences of blockquote tags in the DB with regexp, it’s likely the tags are not in the DB at all, but are inserted by template code or the block editor’s parser like Joy suggested.

Actually, I open any post with status Pubish in DB, and I see that a <blockquote><h5> sequence is there 🙁

I’m stunned…

This is confusing….The tags are in DB, but DB search returns 0 resuts…

Moderator bcworkz
(@bcworkz)

3 years, 5 months ago

Note that in the context of REPLACE, \n is not a regexp, it’s just an escaped character. Now if your SQL version supports REGEXP_REPLACE, use it to search for '<\/blockquote>\s<h5>', which of course IS a regexp that matches any whitespace between the tags. Without REGEXP_REPLACE and \s, try finding different new line variations like ‘\r\n’, ‘\r’, or ‘\n\r’

Short of REGEXP_REPLACE, it still seems like a DB search tool that accepts regexp is still your best bet.

Thread Starter tubella
(@tubella)

3 years, 5 months ago

bcworkz,

huge thanks!

‘\r\n’ worked! all is done clean and nice!

Thanks a lot!

Moderator bcworkz
(@bcworkz)

3 years, 5 months ago

Awesome! You’re welcome.

Viewing 10 replies - 1 through 10 (of 10 total)

The topic ‘SQL Query Updated Revisions, Not Published Posts. How to Fix?’ is closed to new replies.

Views