CODEWITHSUNDEEP
phpmysql
P. Nick
P. Nick

Reputation: 991

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).

Upvotes: 0

Views: 19

Answers (1)

Forbs
Forbs

Reputation: 1276

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)

Upvotes: 1

Related Questions