Which mySQL operator to use when checking for array of variables in a table column

Question

I have a table column which contains a string. This string varies in length depending on the number of variables stored in it, but always follows the same pattern. The string is generated dynamically by a Wordpress plugin and I have no influence over how it is stored. Here it is:

a:3:{i:0;s:3:"509";i:1;s:3:"511";i:2;s:3:"514";}

A user can select any number of variables from a multiple select field in a HTML form. I am storing these in a session:

Array ( [0] => 511 [1] => 514 ) 

The session must contain at least one value, but can contain more.

How do I search for the occurence of 511 or 514 etc. (as stored in my session array) in my table column?

I have so far tried LIKE, IN and EXISTS but none are working.

Please note that I am using WP_query (Wordpress) so the only operators available to me are:

 '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'EXISTS' and 'NOT EXISTS'

Just in case anybody needs it, here's the WP_query meta_query array I'm using:

array(
                    'key' => 'teacher',
                    'value' => $_SESSION['selected_teachers'],
                    'compare' => 'IN'
                )

Many thanks in advance!

Answer 1

try this to generate the WP_query's meta_query

$meta_queries = array( 'relation' => 'OR');   
foreach($_SESSION['selected_teachers'] as $v) {     
    array_push($meta_queries, 
                array(
                    'key' => 'teacher',                             
                    'value' => ':"'.$v.'";',                             
                    'compare' => 'LIKE'                         
    )); 
}

This will generate mysql query using 'WHERE ... LIKE %...%', it will have some performance issues.