Joined table not returning any results

I successfully joined tables in another part of my code so I followed the same pattern but this time it is not returning any result and I don’t understand why.

Here are the tables I am trying to join :

– roles

| id |  type  |
+----+--------+
|  1 | admin  |
|  2 | author |
|  3 | member |
+----+--------+

– users

+----+----------+-------+----------+-------------------+--------+
| id | username | email | password |      role_id      | status |
+----+----------+-------+----------+-------------------+--------+
|    |          |       |          | 3 (default value) |        |
+----+----------+-------+----------+-------------------+--------+

And here is the request :

public function get_users()
{
    $users_list = $this->dbh->query('SELECT users.id, roles.id AS roleid, type, role_id, id, username FROM users LEFT JOIN roles ON users.role_id = roles.id ORDER BY id ASC');
    return $users_list;
}

Answer

Since both 2 tables have id column, it is required to specify which table is referring by <table_name>.id

public function get_users()
{
    $users_list = $this->dbh->query('SELECT users.id AS userid, roles.id AS roleid, type, role_id, username FROM users LEFT JOIN roles ON users.role_id = roles.id ORDER BY users.id ASC');
    return $users_list;
}

Advertisement

Answer