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