Help with a sql query

[-FP]

Some time ago I modified the css so when I used the enter key when writing content, it would just do a line jump without spacing. If I wanted to have an actual space in between 2 lines, people would hit enter 2 times. Now I'm reverting that to the default and... of course...

Could you help me with a query to get rid of all the <p>&nbsp;</p> in the posts table?

[Square Wheels]

I don't recommend it, but here you go

UPDATE `forums_posts` SET `post`= replace(post, '', '<p>&nbsp;</p>')

[-FP]

3 minutes ago, Square Wheels said:

I don't recommend it, but here you go

UPDATE `forums_posts` SET `post`= replace(post, '', '<p>&nbsp;</p>')

And why is that? I plan to make a database backup before doing anything though.

[Square Wheels]

Test it on one post first, add a where clause: 

UPDATE `forums_posts` SET `post`= replace(post, '', '<p>&nbsp;</p>') WHERE pid = xxxx

This way you can ensure you get the result you were hoping for before editing the whole table.

[-FP]

Hmmm, browsing the data of a post in the database, the extra <p> is like this: <p> </p>

There's a space in between, but no &nbsp;

How should I proceed?

[-FP]

Strange, I'm using the SQL Toolbox in the ACP, getting this: Rows matched: 1 Changed: 0 Warnings: 0

For some reason it's not modifying it.

[Jim M]

15 minutes ago, -FP said:

Strange, I'm using the SQL Toolbox in the ACP, getting this: Rows matched: 1 Changed: 0 Warnings: 0

For some reason it's not modifying it.

Means that it only found 1 match to your query. I would suggest using PHPMyAdmin or a tool if you're experimenting as these are full SQL solutions and might provide better answers.

[Flitterkill]

What JIm said. I have the same problem as well and will be doing something similar. The vast majority, if not all, of the imported posts get converted as <p>&nbsp;</p>  - double check the posts when viewing with phpmyadmin or something like that.

[-FP]

Oh I meant that I was actually trying to do it for 1 post only for a test, so yes it found the match, but it looks like the SET command is not doing anything, well, it says "Changed: 0", indeed, the post was not modified.

[-FP]

Same thing in phpmyadmin, it finds the row I want to modify, but it doesn't modify it.

[Flitterkill]

Paste the query you're using here.

[-FP]

This gets me the post I know I want to modify for testing:

SELECT * FROM forums_posts
WHERE pid = 14887;

Yep:

 

Then this to modify it:

UPDATE forums_posts
SET post = REPLACE(post, '<p> </p>', '')
WHERE pid = 14887;

0 rows affected:

[-FP]

Using MariaDB makes any difference?

[Flitterkill]

Shouldn't. I've got a copy of the DB I'll be needing to do this with on the dev box in front of me right now. Trying on my end.

[Flitterkill]

The query is fine  - just used it to correct a mis-spelled word. 

So, it works for words - but <p></p> is a no go. We might need to escape characters? Still investigating.

EDIT: yeah, no surprise it's the brackets.

[Flitterkill]

Well, I always suspected this was going to be messy. Best options may be to dump the db bit to a text file, perl/php edit out the <p></p> bits, and then re-import into MySql.

However... MariaDB to the rescue! 

https://mariadb.com/kb/en/mariadb/pcre/

More specifically:

https://mariadb.com/kb/en/mariadb/regexp_replace/

I'm deving using WAMP so it's a stock MySql and not MariaDB. Until I get serious about upgrading my sites to 4.x I'll probably not touch this problem. Please share your query once you figure out the MariaDB syntax pls :)

[-FP]

Great, MariaDB 10+ is required for REGEXP_REPLACE, jeez, CentOS 7 comes with 5.5 by default, I don't feel like upgrading that now.

[-FP]

Meh, upgraded to MariaDB 10.

This worked @Flitterkill:

UPDATE forums_posts
SET post = REGEXP_REPLACE(post, '<p>.</p>', '')
WHERE pid = 14887;

 

[Flitterkill]

Beautiful. I've rolled out Maria two years ago. Anyone self-hosting and has the ability to do so should. Regex search/replace updates are still not available (without outside plugins) on MySql as far as I can tell.