我有3張桌子notificationsnot_id | not_name ------------------------- 2 | Notification Name 01 3 | Notification Name 02 4 | Notification Name 03 groupsgroup_id | group_name ------------------------- 4 | group name 1 5 | group name 2 group_not---------------------------group_not_id | group_id | not_id --------------------------- 1 | 4 | 2 2 | 4 | 3 3 | 5 | 4 我想顯示與 group_id = 4 的組相關(guān)的所有通知但 php 端顯示重復(fù)如下：Notification NameNotification Name 01 Notification Name 01 Notification Name 02 Notification Name 02MYSQL代碼function getRows_not_group($group_id){ global $conn;$sql = "SELECT group_not.group_not_id, notifications.not_name, groups.group_name FROM group_not JOIN groups ON group_not.group_id = $group_id JOIN notifications ON group_not.not_id = notifications.not_id WHERE group_not.group_id = $group_id"; $result = mysqli_query($conn, $sql); if(!$result) { echo mysqli_error($conn); } $rows = []; if(mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { $rows[] = $row; } } return $rows;}
2 回答
慕標(biāo)琳琳
TA貢獻(xiàn)1830條經(jīng)驗(yàn) 獲得超9個(gè)贊
groups需要修復(fù)引入表的連接條件。
你有：
SELECT ...
FROM group_not
JOIN groups ON group_not.group_id = $group_id --> here
JOIN notifications ON group_not.not_id = notifications.not_id
WHERE group_not.group_id = $group_id
雖然您實(shí)際上需要：
JOIN groups ON group_not.group_id = groups.group_id
我還建議使用表別名來(lái)使查詢(xún)更易于讀寫(xiě)。您還應(yīng)該使用參數(shù)化查詢(xún)，而不是連接查詢(xún)字符串中的變量。所以：
SELECT gn.group_not_id, n.not_name, g.group_name
FROM group_not gn
INNER JOIN groups g ON gn.group_id = g.group_id
JOIN notifications ON gn.not_id = n.not_id
WHERE gn.group_id = ?
郎朗坤
TA貢獻(xiàn)1921條經(jīng)驗(yàn) 獲得超9個(gè)贊
我建議您使用 SELECT DISTINCT 如下：
SELECT DISTINCT group_not.group_not_id, notifications.not_name, groups.group_name FROM group_not JOIN groups ON group_not.group_id = $group_id JOIN notifications ON group_not.not_id = notifications.not_id WHERE group_not.group_id = $group_id";
SELECT DISTINCT 語(yǔ)句用于僅返回不同（不同）的值。
- 2 回答
- 0 關(guān)注
- 113 瀏覽
添加回答
舉報(bào)
0/150
提交
取消