Array of objects returns same values

Question

I use this function to return users with their roles and groups.

I want to return array of objects with correct index, that is reason why I created $index counter.

Problem here is that I get duplicate users if one user have more then 1 groups.

So for example if I have one user with 3 groups, I will get that user 3 times.

How to avoid duplicate users?

I know that somehow I need to check if that user already exists, like I checked for roles, but I'm not sure where.

This code will work if I replace users['$index'] with users['$id'] but on that way I won't get array with correct index, and that is what I need.

    $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
      JOIN user_role as ur 
         ON u.id = ur.userId 
      LEFT 
      JOIN user_group as ug 
         on ug.userId = u.id 
      LEFT 
      JOIN control_group as cg 
         on cg.id = ug.groupId 
     WHERE u.id != ?");
    $stmt->bind_param("i", $_SESSION["id"]);
    $stmt->execute();
    $stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber, 
        $address, $birthDate, $roleName, $groupId, $groupName);
    $users = array();

    $index = 0;

    while ($stmt->fetch()) {
        if (empty($users[$index])) {
            $users[$index] = array(
                'id' => $id,
                'firstName' => $firstName,
                'lastName' => $lastName,
                'email' => $email,
                'phoneNumber' => $phoneNumber,
                'address' => $address,
                'birthDate' => $birthDate,
                'roles' => array(),
                'groups' => array()
            );
        }
        if ($roleName) {
            $found = false;
            foreach ($users[$index]['roles'] as $role) {
                if($role['roleName'] == $roleName){
                    $found = true;
                    break;
                }
            }
            if($found == false)
                $users[$index]['roles'][] = array(
                    'roleName' => $roleName
                );
         }

        if ($groupId) {
            $found = false;
            foreach ($users[$index]['groups'] as $group) {
                if($group['groupName'] == $groupName){
                    $found = true;
                    break;
                }
            }
            if($found == false)
                $users[$index]['groups'][] = array(
                    'groupName' => $groupName
                );
         }
         $index++;
    }

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

So basically i expect object like this

{  
      "id":2,
      "firstName":"Jon",
      "lastName":"Doe",
      "email":"[e-mail]",
      "phoneNumber":"0621-123-444",
      "address":"Address 12a",
      "birthDate":"1976-01-01",
      "roles":['list of role objects'],
      "groups":['list of group objects']
   }

Also i'm not sure if i'm generating object on correct way, i would like if someone can tell me what is right approach and how to properly generate object like this one.

Answer 1

In my opinion, once you get users and database groups using joins, you have more than one index for the same user and you can not change that fact.

So, you have two options. -You can use user_id and unify the groups and roles in an array -Or you can use user_id in the place of the index, and build a list of indexes into the user, in the same way that you ar using a list of roles

But you just need to do this if you realy need to know the index o each user that you get in the database.

I need to understand the context or the user story to have a more accurate idea

OK, I Try something like this:

$index = 0;
$temp_index = 0;
$user_index =[]
while ($stmt->fetch()) {

    if (empty($user_index[$id])){
        $user_index[$id] = $index;
    }else{
        $temp_index = $index;
        $index = $user_index[$id];
    }   

    if (empty($users[$index])) {        
        $users[$index] = array(
            'id' => $id,
            'firstName' => $firstName,
            'lastName' => $lastName,
            'email' => $email,
            'phoneNumber' => $phoneNumber,
            'address' => $address,
            'birthDate' => $birthDate,
            'roles' => array(),
            'groups' => array()
        );
    }
    if ($roleName) {
        $found = false;
        foreach ($users[$index]['roles'] as $role) {
            if($role['roleName'] == $roleName){
                $found = true;
                break;
            }
        }
        if($found == false)
            $users[$index]['roles'][] = array(
                'roleName' => $roleName
            );
     }

    if ($groupId) {
        $found = false;
        foreach ($users[$index]['groups'] as $group) {
            if($group['groupName'] == $groupName){
                $found = true;
                break;
            }
        }
        if($found == false)
            $users[$index]['groups'][] = array(
                'groupName' => $groupName
            );
     }
     if ($tempo_index != 0){
         $index = $temp_index;
         $temp_index = 0;
     }else{
        $index ++;
        $temp_index = 0;
}

Answer 2

If performing multiple queries is an option you could first get all users and then per user the roles and groups.

$user_stmt = $mysqli->prepare("SELECT id, firstName, lastName, email, phoneNumber, address, birthDate FROM users WHERE id != ?");
$user_stmt->bind_param("i", $_SESSION["id"]);
$user_stmt->execute();
$user_stmt->bind_result($id, $firstName, $lastName, $email, $phoneNumber, $address, $birthDate);

$role_stmt = $mysqli->prepare("SELECT roleName FROM user_role WHERE userId = ?");
$group_stmt = ...

$users = array();

while($user_stmt->fetch())
{
    $role_stmt->bind_param("i", $id);
    $role_stmt->execute();
    $role_stmt->bind_result($roleName);

    $roles = array();

    while($role_stmt->fetch())
    {
        $roles[] = array("roleName" => $roleName);
    }

    $groups = array();

    // Same as for roles

    $users[] = array(
            'id' => $id,
            'firstName' => $firstName,
            'lastName' => $lastName,
            'email' => $email,
            'phoneNumber' => $phoneNumber,
            'address' => $address,
            'birthDate' => $birthDate,
            'roles' => $roles,
            'groups' => $groups
        );
    }
}

$user_stmt->close();
$role_stmt->close();
$group_stmt->close();
$mysqli->close();
echo json_encode($users);