Jump to content

Help with a sql query


-FP
 Share

Recommended Posts

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...

p.jpg.4a843afa9cae05b62b3377c52c44df6f.j

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

SELECT * FROM forums_posts
WHERE pid = 14887;

Yep:

Untitled-2.thumb.jpg.3d6cf1a6ed2287fc341

 

Then this to modify it:

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

0 rows affected:

Untitled-3.jpg.79cd65b1a46527970b4a389a0

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...