I have this function that returns me a list of users with their roles and groups. As you can see, this is how I fetch and create list of objects. I'm wondering whether this is a good approach and what parts should be improved. I'm not that experienced with PHP so I would appreciate code samples.I'm also wondering how good an approach it is to first get all users and then make another prepared statement to get user roles and groups. That would mean I will have big number of database calls, so I think it's a bad idea.    $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 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();    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) {            $found = false;            foreach ($users[$id]['roles'] as $role) {                if($role['roleName'] == $roleName){                    $found = true;                    break;                }            }            if($found == false)                $users[$id]['roles'][] = array(                    'roleName' => $roleName                );         }        if ($groupId) {            $found = false;            foreach ($users[$id]['groups'] as $group) {                if($group['groupName'] == $groupName){                    $found = true;                    break;                }            }            if($found == false)                $users[$id]['groups'][] = array(                    'groupName' => $groupName                );         }    }    $stmt->close();    $mysqli->close();    echo json_encode($users);This is the response I get, only thing that I want improve is item index, as you can see in my example as index I have item id, I would like to get correct index starting from 0.
Loading objects for users, roles, and groups from a query with LEFT OUTER JOINs
php;mysql;json;mysqli;join
When you say you want correct index starting from 0, does this mean that you want the first dimension in your data structure to be 0 to n-1 (where n is number of user objects returned)?If that is the case, you really should be building a numerically-indexed array of user objects, not an object/associative array with the user id as the first dimensional key.Some thoughts on your code:I would strongly consider moving away from camelCasing the names of your database entities (table, columns, etc.). This can save you from problem around the fact that, in most cases with MySQL, these database entities are treated without consideration for case (like in files on your system and such).  Using snake_case is generally the preferred approach for most relational database systems to avoid unexpected problems arising from such naming.Be consistent on upper-casing all query parts that are not database entities - AS, ON, etc. in addition to the obvious ones like SELECT, FROM, etc. Right now you are mixing usage for on.Take as much vertical space as you need when writing your queries. You get no bonus points for trying to keep it on as few lines as possible.  Err on the side of making everything in your code more readable.I would consider aliasing the return field names in your query so you can move away from binding query results with variables that are available in the main scope of this script. To me you begin to lose the concept of working with a row of data in your current approach.You might consider using fetch_object() in combination with the aliasing noted above, to give you a nice, readable way to work with each row in the result set. This will also map null values in result set to true null values on the resulting row object.You should use ORDER BY clauses whenever you have cases where you are going to need to map flat rows from a result set into a hierarchical data structure.  This allows you to simply look for changes in column values when iterating the structure to trigger the need to create a new child data structures.If you truly work with objects when reading data into your final structure and use an ORDER BY clause, you should be able to simplify your result row to data structure mapping logic.Why be redundant in your response data with having labels roleName and groupName when they are already nested in roles and groups arrays?Why retrieve group id at all if you are not going to use it in the resulting response structure?Putting this all together you might have code more like this:$query = SELECT    u.id AS id,    u.first_name AS first_name,    u.last_name AS last_name ,    u.email As email,    u.phone_number AS phone_number,     u.address AS address,    u.birth_date AS birth_date,    ur.roleName AS role_name,    cg.name AS group_nameFROM users AS u LEFT OUTER JOIN user_role AS ur ON u.id = ur.user_id LEFT OUTER JOIN user_group AS ug ON ug.user_id = u.id LEFT OUTER JOIN control_group AS cg ON cg.id = ug.group_idWHERE u.id != ?ORDER BY id ASC, role_name ASC, group_name ASC;$stmt = $mysli->prepare($query);$stmt->bind_param(i, $_SESSION[id]);$stmt->execute();// and then fetch rows$users = array();$user = new stdClass();$user->id = 0;while ($row = $stmt->fetch_object()) {    // build new user if needed    if((int)$row->id !== $user->id) {        // Break existing reference between previous $user and $users.        // Data in $users will remain after this dereferencing        unset($user);        $user = new stdClass();        $user->id = (int)$row->id;        $user->firstName = $row->first_name;        $user->lastName = $row->last_name;        $user->email = $row->email;        $user->phoneNumber = $row->phone_number;        $user->address = $row->address;        $user->birthDate = $row->birth_date;        $user->roles = array();        $user->groups = array();        // Assign this new user object by reference to $users.        // This allows you to simply work with $user here in loop        // as opposed to $users[$index] which requires you to manually        // track index values.        $users[] =& $user;        // since this is a new user object, we need to reset        // role and group objects so we can detect changes in these columns.        unset($role);        $role = new stdClass();        $role->name = null;        unset($group);        $group = new stdClass();        $group->name = null;    }    // build new role if needed    if($row->role_name !== $role->name) {        // dereference role        unset($role);        // create new role for this user        $role = new stdClass();        $contact->name = $row->role_name;        $user->roles[] =& $role;    }    // build new group if needed    if($row->group_name !== $group->name) {        // dereference group        unset($group);        // create new group for this user        $group = new stdClass();        $group->name = $row->group_name;        $user->groups[] =& $group;    }}unset($user, $role, $group);$stmt->close();$mysqli->close();echo json_encode($users);Note that this is simple example and doesn't have proper error handling around statement preparation and execution.  You should make sure that you just don't assume these things work.  Make sure you understand all possible results and/or exceptions that can occur from a function/method call and handle those outcomes accordingly.