I want to check in mysql if all given keys exists in set or not. like:
$comma_separted_user_ids = "20,2,9,8,31,1";
$query ="SELECT conversation_id FROM message
WHERE FIND_IN_SET($comma_separted_user_ids, user_ids) ";
// data of user_ids = "1,2,8,9,20,31";
I want to check if all user id exist in user_ids column or not, user_ids are not properly ordered.
Please suggest a solution, thanks.
While it is technically feasible:
$query =
'SELECT conversation_id FROM message'
. 'WHERE FIND_IN_SET('
. str_replace(
',',
', user_ids) AND FIND_IN_SET('
$comma_separted_user_ids
)
. ', user_ids)' ;
... you should never do this!
Instead, create a new table to model the many-to-many relationship that exists between your user and message entities (e.g. participant). This is basic normalisation.
Then the query becomes trivial and performant:
SELECT conversation_id FROM participant
WHERE user_id IN ($comma_separted_user_ids)
GROUP BY conversation_id
HAVING COUNT(user_id) = [number of items in $comma_separted_user_ids]
Since you don't know the ordering, I don't see a way around FIND_IN_SET. Like others said, it'd be far better to normalise your table structure.
But in the interest of providing an answer to the question, you'll need to create a list of FIND_IN_SET operators.
// A list of IDs.
$comma_separated_user_ids = "20,2,9,8,31,1";
// The TRUE string will make sure that the array
// always contains at least one item.
$where = array("TRUE");
// Iterate over the IDs and create strings such as
// "FIND_IN_SET(1, column_name_here)"
foreach(explode(",", $comma_separated_user_ids) as $id) {
$where[] = "FIND_IN_SET($id, user_ids)";
}
Then it's a simple matter of joining the strings together:
// Join everything together with AND (&&).
// Since "0" is considered FALSE, this works.
$where = implode(" && ", $where);
// Query for rows.
$query ="SELECT conversation_id FROM message WHERE ($where) ";
Don't use this if you don't need to. It won't scale very well.
You can do this:
SELECT conversation_id
FROM message
WHERE FIND_IN_SET($comma_separted_user_ids, user_ids) > 0
GROUP BY conversation_id
HAVING count(distinct user_id) = 1 + (length($comma_separted_user_id) - length(replace($comma_separted_user_id, ',', '')))
The having clause is counting the number of elements in the comma separated list.
If you are creating the SQL, you should consider using a table to store the values instead of a list. A join approach can take advantage of indexes, which find_in_set() cannot.
i guess you should write it like this :
$comma_separted_user_ids = "20,2,9,8,31,1";
$query ="SELECT conversation_id FROM message
WHERE user_id IN ($comma_separted_user_ids) ";
