首頁猿問加入的表不返回任何結(jié)果

加入的表不返回任何結(jié)果

PHP

料青山看我應(yīng)如是 2022-10-09 19:53:41

我在代碼的另一部分成功地加入了表格，所以我遵循了相同的模式，但這次它沒有返回任何結(jié)果，我不明白為什么。以下是我要加入的表格：- 角色| 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;}

查看完整描述

3 回答

森林海

TA貢獻2011條經(jīng)驗獲得超2個贊

SQL應(yīng)該是

SELECT

u.id,

r.id AS roleid,

u.type,

r.username

FROM users u

LEFT JOIN roles r ON r.id = u.role_id

ORDER 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

反對回復(fù) 2022-10-09

慕的地10843

TA貢獻1785條經(jīng)驗獲得超8個贊

像這樣試試

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;

}

反對回復(fù) 2022-10-09

尚方寶劍之說

TA貢獻1788條經(jīng)驗獲得超4個贊

由于兩個表都有 id 列，因此需要指定哪個表引用<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;

}

反對回復(fù) 2022-10-09