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":"[email protected]",
      "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.

I get output like this paste.ofcode.org/4irsgF4s4qcnerArP29NfA, as you can see user Mark have 2 groups that is reason why he is showed 2 times... — Sahbaz
– Sahbaz, Commented Feb 26, 2017 at 15:32
@SuperMario'sYoshi from ur query r you fetching data of one user or many users....and do you mean it returns 3 rows for one user...? — Naincy
– Naincy, Commented Feb 26, 2017 at 17:05
I'm fetching data for many users, so there is list of users with potentially many groups and roles, i'm expecting response like this paste.ofcode.org/38W4uJDB3hPKsthh8aVdFSx , only problem there is as you can see i have user id as index, but i don't want to have user id as index, i want to have items starting from 0 — Sahbaz
– Sahbaz, Commented Feb 26, 2017 at 17:08
$res = []; foreach($users as $user) { $res[] = $user; } echo json_encode($res); The output you shown in above link am considering it in $users and after you logic do above code and $res will be result that you want — Naincy
– Naincy, Commented Feb 26, 2017 at 17:19

Moritz · Accepted Answer · 2017-02-26 15:17:59Z

1

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

answered Feb 26, 2017 at 15:17

Moritz

721 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sahbaz Over a year ago

I considered that, but in terms of performance how good solution is that, on this way i have one DB call and on that way i will need 2 or 3 depends on how object look. so my main issue here is, is it better to have one big query with joins, or multiple simple queries ?

Marcelo Araujo · Accepted Answer · 2017-02-26 16:27:15Z

0

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;
}

edited Feb 26, 2017 at 16:27

answered Feb 26, 2017 at 15:11

Marcelo Araujo

11 bronze badge

5 Comments

Sahbaz Over a year ago

I'm not sure if i totally understand what do you mean, but basically i just want to return list of users with correct index, so for example if i have 4 users i want to return array with starting index 0, IF i use user id, i will get array of objects but without correct index, instead i will get userId as index and i dont want that.

gaurav Over a year ago

than don't use index just use $users[] to entering in users array

Sahbaz Over a year ago

Hmm im not sure how i can do it, because users have multiple roles and groups, and i need to fetch it into user object?

Marcelo Araujo Over a year ago

basically the index is the amount of lines that you picked up into the database. When you join two tables like users and groups, normally the db will return more then one instance of user for each role or for each group. Because each user has more then one role or group, so, is like a multiplication where users appear more then one time. One User X One Group = One Line, One user X Two Groups = Two Lines and so on I just don't understand why you need the index if what you, apparently want, is to know if the user with id 349, for example, can access some resource or page.

Sahbaz Over a year ago

I just want to have correct array for example if i return 5 users i want to get response of arrays starting from 0 and not from user id..

Collectives™ on Stack Overflow

Array of objects returns same values

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related