P. Nick P. Nick - 3 months ago 9
MySQL Question

PHP - Searching for multiple rows with numbers/commas

I've made a user group and a user table in my database, called

test
and
user_test
. Every user has a field called
groups
which countains at least one number, but could also contain multiple numbers, for example
1,2,3
. The user group table exists of
id
and
group_name
.

What I've been trying to do for so long now is to get data from all groups that this user is assigned to. For example, if one user is assigned to groups
1,2,3
(as its shown in the database), it will print out the name of each group with those id. So perhaps it'd print out
group 1
group 2
group 3
.

$user_test = $this->mysqli->query("SELECT user_id,groups FROM user_test WHERE user_id = '1'");
while($user_test_fetch = $user_test->fetch_array()) {
$groups = $user_test_fetch["groups"];
}

if(strlen($groups) > 1) { // user has more than 1 group
// ???
} else { // user does not have more than 1 group
$search = "id = '".$groups . "'";
}

$group_data = $this->mysqli->query("SELECT * FROM test WHERE ".$search."");
while($group_data_fetch = $group_data->fetch_array()) {
echo $group_data_fetch["group_name"];
}


Or if you have any other way you'd do this task, please feel free to show me! I'm just simply trying to learn how to do this task (preferably, as efficient as possible).

Answer

Pretty simple.
If it is stored in the 1,2,3 format. And assuming a single one is called 1 and id is an INT or BIGINT (otherwise this query will slow down if it is a VARCHAR)

Change

if(strlen($groups) > 1) { // user has more than 1 group
// ???
} else { // user does not have more than 1 group
$search = "id = '".$groups . "'";
}

to

$search = "id IN (".$groups.");

this single line will work with a single group or a set of groups, as long as it is separated by a comma (Because SQL loves commas)