-FP Posted October 14, 2015 Posted October 14, 2015 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> </p> in the posts table?
Square Wheels Posted October 14, 2015 Posted October 14, 2015 I don't recommend it, but here you go UPDATE `forums_posts` SET `post`= replace(post, '', '<p> </p>')
-FP Posted October 14, 2015 Author Posted October 14, 2015 3 minutes ago, Square Wheels said: I don't recommend it, but here you go UPDATE `forums_posts` SET `post`= replace(post, '', '<p> </p>') And why is that? I plan to make a database backup before doing anything though.
Square Wheels Posted October 14, 2015 Posted October 14, 2015 Test it on one post first, add a where clause: UPDATE `forums_posts` SET `post`= replace(post, '', '<p> </p>') WHERE pid = xxxx This way you can ensure you get the result you were hoping for before editing the whole table.
-FP Posted October 14, 2015 Author Posted October 14, 2015 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 How should I proceed?
-FP Posted October 14, 2015 Author Posted October 14, 2015 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 Posted October 14, 2015 Posted October 14, 2015 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 Posted October 14, 2015 Posted October 14, 2015 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> </p> - double check the posts when viewing with phpmyadmin or something like that.
-FP Posted October 14, 2015 Author Posted October 14, 2015 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 Posted October 14, 2015 Author Posted October 14, 2015 Same thing in phpmyadmin, it finds the row I want to modify, but it doesn't modify it.
-FP Posted October 14, 2015 Author Posted October 14, 2015 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:
Flitterkill Posted October 14, 2015 Posted October 14, 2015 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 Posted October 14, 2015 Posted October 14, 2015 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 Posted October 14, 2015 Posted October 14, 2015 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 Posted October 14, 2015 Author Posted October 14, 2015 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 Posted October 14, 2015 Author Posted October 14, 2015 Meh, upgraded to MariaDB 10. This worked @Flitterkill: UPDATE forums_posts SET post = REGEXP_REPLACE(post, '<p>.</p>', '') WHERE pid = 14887;
Flitterkill Posted October 15, 2015 Posted October 15, 2015 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.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.