Fetch all records from MySQL as lazy="false" with PHP

Question

I have 2 tables in my database users and users_roles, like so:

users:

id | username | user_role_id
----------------------------
 1 | User1    | 1
 2 | User2    | 1
 3 | User3    | 2

users_roles:

id | role 
----------
 1 | admin
 2 | user

So, in Java I just had to write in Hibernate mappings an attribute lazy="false" to just get a list of all users automatically containing users_roles objects too, each specifically for each record in users table, but in PHP? I just came across an option in the PDO called FETCH_ASSOC, but I cannot understand how it works.

My get all users list method looks like this:

public function getAll() {

    $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);

    $sql = $conn->prepare("SELECT * FROM users ORDER BY id DESC");

    $sql->execute();
    $list = $sql->fetchAll(PDO::FETCH_OBJ);

    $conn = null;

    return $list;
}

and the piece of code from the Smarty template:

{foreach from=$usersList item=item}                
    {$item->username}
    {$item->users_roles.role}
{/foreach}

Would you mind helping me a little to get on the right way to it?

Answer 1

FETCH_ASSOC option fetches the rows into an associative array like:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => name1
        )

    [1] => Array
        (
            [id] => 2
            [name] => name2
        )
)

If you want top get all the users, who have a corresponding record in users_roles table, you can use this request:

SELECT 
    *
FROM
    users
INNER JOIN
    users_roles
ON
    users_roles.id=users.user_role_id
ORDER BY
    users.id DESC