猿问

加入的表不返回任何结果

我在代码的另一部分成功地加入了表格,所以我遵循了相同的模式,但这次它没有返回任何结果,我不明白为什么。


以下是我要加入的表格:


- 角色


| id |  type  |

+----+--------+

|  1 | admin  |

|  2 | author |

|  3 | member |

+----+--------+

- 用户


+----+----------+-------+----------+-------------------+--------+

| id | username | email | password |      role_id      | status |

+----+----------+-------+----------+-------------------+--------+

|    |          |       |          | 3 (default value) |        |

+----+----------+-------+----------+-------------------+--------+

这是请求:


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;


料青山看我应如是
浏览 124回答 3
3回答

森林海

SQL应该是SELECT      u.id,     r.id AS roleid,     u.type,    r.usernameFROM users u    LEFT JOIN roles r ON r.id = u.role_idORDER BY u.id ASC 我省略了您在 sql 中定义的一些列,因为我根本看不到它们的意义。不仅如此,它们可能看起来模棱两可。例如:SELECT     users.id,             // User ID    roles.id AS roleid,   // Role ID    type,                 // User type    role_id,              // Role ID (We already have it, no need for it again)    id,                   // What ID? This is ambiguous and will fail     username              // User Username

慕的地10843

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

尚方宝剑之说

由于两个表都有 id 列,因此需要指定哪个表引用<table_name>.idpublic function get_users(){&nbsp; &nbsp; $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');&nbsp; &nbsp; return $users_list;}&nbsp;
随时随地看视频慕课网APP
我要回答