CODEWITHSUNDEEP
phpmysqljson
samjones39
samjones39

Reputation: 163

how to select exact json value in mysql query condition

I am trying to select exact json value in mysql query condition, currently i am using LIKE and it work but like returns false positive.

my code is::

$id='1';

json data example:: ["1","12","38"]

$sql="SELECT * FROM `posts` WHERE `json_column` LIKE '%".$id."%' ";

with this query, it returns both 1 and 12. how can i get just 1?

Upvotes: 0

Views: 861

Answers (1)

thodic
thodic

Reputation: 2269

Note the extra quotes:

$sql="SELECT * FROM posts WHERE json_column LIKE '%\"$id\"%'";

However, this is wide open to SQL inject if $id comes from user input. Be careful.

The more secure method would be to use parameterised queries with PHPs prepared statements as follows:

$stmt = $dbh->prepare("SELECT * FROM posts WHERE json_column LIKE '%\"?\"%'");
$stmt->bindParam(1, $id);

Upvotes: 1

Related Questions