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!

That looks like a php serialised array. If you are being hamstrung by Wordpress like that it might be possible to write a MySQL function to do the check, — Kickstart, Nov 17 '15 at 13:58
is your _$_SESSION['selected_teachers']_ is string _a:3:{i:0;s:3:"509";i:1;s:3:"511";i:2;s:3:"514";}_ or _Array ( [0] => 511 [1] => 514 )_ ? — David, Nov 17 '15 at 16:12
@David: $_SESSION['selected_teachers'] is Array ( [0] => 511 [1] => 514 ) — Richard Tinkler, Nov 17 '15 at 16:24

score 1 · Accepted Answer · answered Nov 17 '15 at 17:14

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.

score 0 · Answer 2 · edited Apr 13 '17 at 12:37

0

I think you can find the answer to your question here: https://wordpress.stackexchange.com/questions/18703/wp-query-with-post-title-like-something

[sorry for writing an answer, but I can't comment yet]

edited Apr 13 '17 at 12:37

Community

1
1

answered Nov 17 '15 at 15:35

TBuc

86
1
6

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

2 Answers2