Super Mario's Yoshi Super Mario's Yoshi - 4 years ago 109
MySQL Question

PHP fetch from many to many

I have issue with this PHP function, i'm trying to fetch user with his roles and groups, its working good with roles, but i have issues with groups because i'm getting array with same groups and of course what i want is duplicate free array. I understand why i'm getting this, because my user have 2 roles and i get him in sql query 2 times, can anyone explain me what is best practice and how to avoid this, should i use query like this, or i can use anther query where i will find all groups for member and then just place it in that array, but that is 2 DB calls in one function and its probably not that good solution.

function getAll($mysqli) {
$stmt = $mysqli->prepare("SELECT u.id, u.firstName, u.lastName, u.email,
u.phoneNumber, u.address, u.birthDate, ur.roleName, cg.id, cg.name FROM users as u
LEFT OUTER JOIN user_role as ur ON u.id = ur.userId
LEFT OUTER JOIN user_group as ug on ug.userId = u.id
LEFT OUTER JOIN control_group as cg on cg.id = ug.groupId");

$stmt->execute();
$stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber,
$address, $birthDate, $roleName, $groupId, $groupName);
$users = array();

while ($stmt->fetch()) {
if (empty($users[$id])) {
$users[$id] = array(
'id' => $id,
'firstName' => $firstName,
'lastName' => $lastName,
'email' => $email,
'phoneNumber' => $phoneNumber,
'address' => $address,
'birthDate' => $birthDate,
'roles' => array(),
'groups' => array()
);
}
if ($roleName) {
$users[$id]['roles'][] = array(
'roleName' => $roleName
);
}
if ($groupId) {
$users[$id]['groups'][] = array(
'groupName' => $groupName
);
}
}

$stmt->close();
$mysqli->close();;
echo json_encode($users);
}


When i execute that function i get response like this

{
"1":{
"id":1,
"firstName":"John",
"lastName":"Doe",
"email":"john@email.com",
"phoneNumber":"062-441234-123123",
"address":"Some Address 40\/2",
"birthDate":"1989-12-29",
"roles":[
{
"roleName":"Admin"
},
{
"roleName":"User"
}
],
"groups":[
{
"groupName":"Group 1"
},
{
"groupName":"Group 1"
}
]
},
"2":{
"id":2,
"firstName":"Jane",
"lastName":"Doe",
"email":"jane@email.com",
"phoneNumber":"0112-11121-221322",
"address":"Address 21ca",
"birthDate":"1975-12-28",
"roles":[
{
"roleName":"Admin"
},
{
"roleName":"User"
}
],
"groups":[

]
}
}

Answer Source

You could check if a role (or a group) already exists before inserting it in the array:

if ($roleName) {
    $found = false;
    foreach ($users[$id]['roles'] as $role) {
        if($role['roleName'] == $roleName){
            $found = true;
            break;
        }
    }
    if($found == false)
        $users[$id]['roles'][] = array(
            'roleName' => $roleName
        );
 }

Notice I gave you an answer based in your structure. But I would strongly advice to change the structure of the array to make the checking of the existence of the role as fast as O(1)

Edit: In order to complete my answer: I would say the JSON structure doesn't reflect what the real case is. You state a user has roles and groups, and that's true, but the roles belong to the group. A user can be an ADMIN in a Group 1 and ANYTHING in Group 2. That's why I think what you should really output is something like this:

{  
   "1":{  
      "id":1,
      "firstName":"John",
      "lastName":"Doe",
      "email":"john@email.com",
      "phoneNumber":"062-441234-123123",
      "address":"Some Address 40\/2",
      "birthDate":"1989-12-29",
      "groups":[  
         {  
            "groupName": "Group 1",
            "roles": [
                {  
                    "roleName":"Admin"
                },
                {  
                    "roleName":"User"
                }
            ]
         }

      ]
   },
   "2":{  
      "id":2,
      "firstName":"Jane",
      "lastName":"Doe",
      "email":"jane@email.com",
      "phoneNumber":"0112-11121-221322",
      "address":"Address 21ca",
      "birthDate":"1975-12-28",
      "groups":[  

      ]
   }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download