首頁猿問 php mysql 我托盤加入 3...

php mysql 我托盤加入 3 個表，但顯示重復的結果

PHP

猛跑小豬 2023-09-15 14:36:51

我有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 的組相關的所有通知但 php 端顯示重復如下：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 回答

慕標琳琳

TA貢獻1830條經驗獲得超9個贊

groups需要修復引入表的連接條件。

你有：

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

雖然您實際上需要：

JOIN groups ON group_not.group_id = groups.group_id

我還建議使用表別名來使查詢更易于讀寫。您還應該使用參數化查詢，而不是連接查詢字符串中的變量。所以：

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 = ?

反對回復 2023-09-15

郎朗坤

TA貢獻1921條經驗獲得超9個贊

我建議您使用 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 語句用于僅返回不同（不同）的值。

反對回復 2023-09-15