I am aware of the MySQL function IN(), which tries to match a single column value with an array of given values ( in this case we're using a PHP codebase and $ids = [1,2,3,4]; ):
SELECT * FROM galleries WHERE id IN ($ids)
If my column 'id' has multiple values in it, with each value being separated by a comma, e.g. 1,3,4,6,14 ... how can I write a SQL statement to compare all the values in my $ids array with all the values in this column?
In order to get this to work, you are going to need to split your array $ids into individual values.
If the match criteria is that every value in your array appears in the column, you'll need to check each value. You may be able to make use the MySQL FIND_IN_SET function.
Reference: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set
For example, let's assume that your array $ids contains four elements: '2','3','5','7'.
To check whether each of those values is contained within another string, you'd need SQL that does something like this:
WHERE FIND_IN_SET( '2', '1,2,3,4,5,6,7,8')
AND FIND_IN_SET( '3', '1,2,3,4,5,6,7,8')
AND FIND_IN_SET( '5', '1,2,3,4,5,6,7,8')
AND FIND_IN_SET( '7', '1,2,3,4,5,6,7,8')
I used a literal string as the second argument in the FIND_IN_SET there to show what was going on, that literal represents the "comma separated list of values" stored your column. Obviously, your query would replace that literal with a reference to your column:
WHERE FIND_IN_SET( '2', t.mycol)
AND FIND_IN_SET( '3', t.mycol)
AND FIND_IN_SET( '5', t.mycol)
AND FIND_IN_SET( '7', t.mycol)
You could also use a LIKE function, if you add leading and trailing commas to the column, and the value
WHERE CONCAT(',','1,2,3,4,5,6,7,8',',') LIKE CONCAT('%,','2',',%')
AND CONCAT(',','1,2,3,4,5,6,7,8',',') LIKE CONCAT('%,','3',',%')
AND CONCAT(',','1,2,3,4,5,6,7,8',',') LIKE CONCAT('%,','5',',%')
AND CONCAT(',','1,2,3,4,5,6,7,8',',') LIKE CONCAT('%,','7',',%')
Again, the literal of the "list of comma separated values" represents your column, the individual values '2', '3', '5', '7' represent the values from the elements of your array $ids.
(If a "match" requires finding just one of the values in the list, and not all values, then replace the AND with OR.)
If you are looking for an "exact match" of the elements; that is, there shouldn't be any values in the "comma separated list of values" that are not also a value in the array, you'd need to combine the elements from the array into a comma separated list, so the query would be something like this:
WHERE '2,3,5,7' = '1,2,3,4,5,6,7,8'
If the order of the values stored in the column isn't canonical, then you'd need to check all the possible permutations...
WHERE '2,3,5,7' = '1,2,3,4,5,6,7,8'
OR '2,5,3,7' = '1,2,3,4,5,6,7,8'
OR '2,5,7,3' = '1,2,3,4,5,6,7,8'
...
For a lot of values, I'd probably go with making both "lists of values" canonical (ordering the values in each list.)
If this looks ugly, it's because it is ugly. And it's going to be horrible performance on large sets. SQL isn't designed to handle manipulating a "set" as a comma separated list. It's designed to handle a "set" of values as a set of rows.
Bill Karwin has an excellent book available. Chapter 2 covers issues with the "comma separated list" pattern.
http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557
What I would do is kind of a hack.
1. Create a temporary table (name it T) with one field and each row will have one value from your $ids.
2. Join your table with this temporary table and in the condition use find_in_set
$sql = "CREATE TEMPORARY TABLE T (v1 INT)";
//run sql
$sql = "INSERT INTO T (" . explode('),(',$ids) . ')';
//run sql
$sql = "SELECT DISTINCT galleries.* from galleries JOIN T ON FIND_IN_SET(T.v1,galleries.set_field_values)
