Mysql compare json column with array

Viewed 1358

I'm working on a PHP PDO query and I want to check if a JSON column intersects with a PHP array.

$classes = [1,2,3,4,5,6,7];
|---------------------|------------------|
|      students       |       classes    |
|---------------------|------------------|
|          12         |      [1,3,6]     |
|---------------------|------------------|
|          13         |     [2,9,10]     |
|---------------------|------------------|
|          14         |     [9,8,10]     |
|---------------------|------------------|

for example in the example above i need to get all student with at least one classe exist on the $classes = [1,2,3,4,5,6,7]; array so in this case the result should be :

|---------------------|------------------|
|      students       |       classes    |
|---------------------|------------------|
|          12         |      [1,3,6]     |
|---------------------|------------------|
|          13         |      [2,9,10]    |
|---------------------|------------------|

I tried to make the array as a string and do a "%like%" but it not working because of 'x,y,z' is not in 'a,b,x,c'.

so I was wondering if we can compare two arrays on stored in MySQL as json and the other is a PHP array. and I need to do that inside the query.

thanks

1 Answers

Have you tried using JSON_EXTRACT (link)?

It should let you treat it as an array.

DigitalOcean HowTo (I am not affiliated with them in anyway).

Related