1

i'm using that MySQL Query to format my wordpress post. It worked very well for characters like " and others, but not for "»". I made research to fix the problem, but i did not find a solution, also not here on stackoverflow.

When i execute this code nothing happend.

$query = "UPDATE wp_posts SET post_content = REPLACE(post_content, '»<br />', '»\n\n')";

I tried this

$query = "UPDATE wp_posts SET post_content = REPLACE(post_content, '\»<br />', '\»\n\n')";

and that

$query = "UPDATE wp_posts SET post_content = REPLACE(post_content, '&raquo;<br />', '&raquo;\n\n')";

It is not working.

benoa3
  • 11
  • 1
  • Can you use placeholder values? If so, this should be easy to do. Encoding it inside a double-quoted string is messing things up SQL statement-wise. Consider `REPLACE(content, ?, ?)` and then bind those two parameters. – tadman Nov 04 '20 at 19:30

1 Answers1

0

You must escape the \n

CREATE TABLE wp_posts (post_content varchar(29))

INSERT INTO wp_posts VALUES('a»<br />b')

UPDATE wp_posts SET post_content = REPLACE(post_content, '»<br />', '»\\n\\n')

SELECT * FROM wp_posts

| post_content |
| :----------- |
| a»\n\nb     |

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47
  • I don't think a literal *backslash `n`* is the desired outcome here. – tadman Nov 04 '20 at 19:29
  • Thanks for your answer, your solution did not work for me. I will try $conn->set_charset("utf8"); Maybe that helps – benoa3 Nov 04 '20 at 19:47
  • yes if you have utf8 then [utf8 all the way](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) through is the only way – nbk Nov 04 '20 at 19:51
  • 1
    This is working $query = "UPDATE wp_posts SET post_content = REPLACE(post_content, '»
    ', '»\n\n')"; when i set mysqli_set_charset($conn,"utf8");     – benoa3 Nov 04 '20 at 19:52
  • leave the double \ as you they don't hurt – nbk Nov 04 '20 at 19:55