Mysql query...WHERE (comma separated string)IN(Comma separated string)

Question

I am trying to pull ids from table where numbers in comma-separated string 1, are all in comma-separated string 2. The IN function gives me ids if any numbers match, but I want all from string 1 to match or that id is not shown in result.

$query = "SELECT teams FROM #__bl_teamcord WHERE u_id = ".$t_id." AND s_id = ".$sid." LIMIT 1";
    $db->setQuery($query);
    $tcteams = $db->loadResult();


$query = "SELECT r.id FROM  #__bl_regions as r WHERE (NOW() between r.start_date and r.end_date) AND r.s_id = ".$sid."  AND (r.teams IN($tcteams))";
    $db->setQuery($query);
    $regions1 = $db->loadResultArray();

are you trying to do this? "1,2,3" = "1,2,3" or 1 in ["1,2,3".split(",")] — Churk, Apr 03 '12 at 17:23
Where 1,2,3 are in 5,16,2,78,1,54,34,3 .....May not be in 1,2,3 same order but each value is in second table. — user1071915, Apr 03 '12 at 17:35

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

Log story short, No MySQL can not split your string and try to do an IN clause. But there is a way around it. And here is the reference. http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Original Reference: Can Mysql Split a column?

edited May 23 '17 at 12:33

Community

1
1

answered Apr 03 '12 at 17:19

Churk

4,556
5
22
37

Mysql query...WHERE (comma separated string)IN(Comma separated string)

1 Answers1