PHP MySQL Data Group

Question

I have sql data as follows:

+++++++++++++++++++++++++++++++++
+ id        name          group +
+  1        cs1           1,2   +
+  2        cs2           1,3   +
+  3        cs3           2,3   +
+++++++++++++++++++++++++++++++++

What I wanted is to select the names for every group which is represented by group IDs in the group column. I have searched so much but can't figure out how to get the data for each group using PHP.

I want the output to be:

++++++++++++++++++++++++++++++++
+group                members  +
+   1                 cs1      +
+                     cs2      +
+   2                 cs2      +
+                     cs3      +
+   3                 cs2      +
+                     cs3      +
++++++++++++++++++++++++++++++++

or

$group1 = array (cs1, cs2);

Your valuable insights are highly appreciated.

Thank you.

you have difficulties to search for a solution because you have a poor database design. Never store different values in a single cell comma separated. use separate fields. Otherwise this architecture will lead you to many headaches like this — Lelio Faieta, Oct 03 '18 at 07:42
Consider changing that comma seperated list of keys into a link table — RiggsFolly, Oct 03 '18 at 07:45
https://database.guide/the-3-types-of-relationships-in-database-design/ — RiggsFolly, Oct 03 '18 at 07:46

score 0 · Answer 1 · answered Oct 03 '18 at 08:08

Using PHP you can read the data, get the groups and then find each member to build an array of group mapping to members...

$data = [
    [
        'id'    => 1,
        'name'  => 'cs1',
        'group' => '1,2'
    ],
    [
        'id'    => 2,
        'name'  => 'cs2',
        'group' => '1,3'
    ],
    [
        'id'    => 3,
        'name'  => 'cs3',
        'group' => '2,3'
    ]
];

$groups = [];
foreach ($data as $item) {
    foreach (explode(',', $item['group']) as $group) {
        $groups[] = $group;
    }
}
$groups = array_unique($groups);
sort($groups);
$group_members = [];
foreach ($groups as $group) {
    $group_members[$group] = [];
    foreach ($data as $item) {
        if (strpos(",{$item['group']},", ",{$group},") !== false) {
            $group_members[$group][] = $item['name'];
        }
    }
}
var_dump($group_members);

PHP MySQL Data Group

1 Answers1