MYSQL (WHERE IN) in MySQL

Question

I have session stored with value (not array, just text) : '1, 2'.

$_SESSION['locs'] = '1, 2';

And i have mysql table called (operators) with this fields:

op_id, op_name, location

1 sami 1, 3, 5

2 foo 1

3 boo 4, 5

I want to get results where any number in location is exist in session value.

I tried with WHERE IN statements but I don't know how to take each number session or location.

Can any body help me.

Sami

Answer 1

You may explode your session value to array:

$locs = explode(', ' $_SESSION['locs']);

And SQL was follow:

$where = "WHERE ";
$count = count($locs);
for ($i = 0; $i < $count; $i++) {
    $where .= "location LIKE '%, ". $locs[$i] .",%' ";
    $where .= " OR location LIKE '%, ". $locs[$i] ."%' ";
    if ($i < $count - 1) {
        $where .= " OR ";
    }
}

Or use MySQL RegEx for regular expression search

Better way is to use REGEXP:

$where = "WHERE ";
$count = count($locs);
for ($i = 0; $i < $count; $i++) {
    $where .= "location REGEXP '(,[[:blank:]]*|^)". $locs[$i] ."(,|$)'";
    if ($i < $count - 1) {
        $where .= " OR ";
    }
}

I tested them, it's work on "1, 2, 3" string for any number.