Php/MySql find/replace utf-8 string in json

Question

I have this json string in a row in mysql database

{"editor":"<p>\u067e\u062f\u0631\u0627\u0645<\/p>"}

And want to find this and replace with <a> tag, but for test, I just select it but look like it can't find it, I used this query:

SELECT * FROM `wp_postmeta` WHERE `meta_value` LIKE '%\u067e\u062f\u0631\u0627\u0645%'

What is the problem?

This is original query:

$keyword_j = "\u067e\u062f\u0631\u0627\u0645";

$sql2 = "UPDATE `wp_postmeta` SET `meta_value` = REPLACE(meta_value, '".$keyword_j."', '<a href=\"\/".$link."\">".$keyword_j."<\/a>') WHERE `meta_value` LIKE '%".$keyword_j."%'";

Chilarai · Accepted Answer · 2020-07-27 14:04:23Z

1

You need to put escape sequence to achieve this. In Mysql, there is a second layer of escaping involved. So use this

SELECT * FROM `wp_postmeta` WHERE `meta_value` LIKE '%\\\\u067e\\\\u062f\\\\u0631\\\\u0627\\\\u0645%'

To automatically search and replace slash, use this

<?php
$keyword_j = "\u067e\u062f\u0631\u0627\u0645";
$x = str_replace("\\", "\\\\\\\\", $keyword_j);
echo $x;
?>

Update Query

$str = addslashes(htmlentities("<a href='".$link."'>".$keyword_j."</a>"));

$sql2 = "UPDATE wp_postmeta SET meta_value ='".$str."' WHERE meta_value LIKE '%".$x."%'";

edited Jul 27, 2020 at 14:04

answered Jul 27, 2020 at 7:41

Chilarai

1,8882 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jack The Baker Over a year ago

Right. with this query it found. but one thing, how can I escape variable? $keyword_j ? I can not do this manually, I mean add some slashes, is there any way to do this automatically?

Jack The Baker Over a year ago

Okay, nice. one last thing, in UPDATE query, should escape everywhere? I used $keyword_j 3 times, should use $x instead of all? or just use in LIKE ? because I used $x for all, it didn't nothing

Chilarai Over a year ago

No. your existing update query is fine. Don't change it. It should not be $x. In your case $keyword_j is fine for update query

Jack The Baker Over a year ago

But it won't update, nothing changed with UPDATE query. It run without error but nothing changed

Collectives™ on Stack Overflow

Php/MySql find/replace utf-8 string in json

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related