php mysql I tray to join 3 table but in show duplicated result

Question

I have 3 tabels

notifications

not_id    |     not_name    
-------------------------
  2       |   Notification Name 01  
  3       |   Notification Name 02      
  4       |   Notification Name 03

groups

group_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

I want to show all notification related to group which have group_id = 4

but the php side show duplicated as below :

Notification Name

Notification Name 01    
Notification Name 01    
Notification Name 02    
Notification Name 02

MYSQL code

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;
}

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 05 '20 at 13:07

score 0 · Accepted Answer · answered Aug 04 '20 at 21:42

The join condition that brings in table groups needs to be fixed.

You have:

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

While you actually need:

JOIN groups ON group_not.group_id = groups.group_id

I would also recommend using table aliases to make the query easier to read and write. You should also use a parameterized query rather than concatenating the variable in the query string. So:

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

thanks so much i use (JOIN groups ON group_not.group_id = groups.group_id ) and its work — Allawnah, Aug 04 '20 at 23:19

score -1 · Answer 2 · answered Aug 04 '20 at 22:07

I suggest you to use SELECT DISTINCT AS BELOW:

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";

The SELECT DISTINCT statement is used to return only distinct (different) values.

php mysql I tray to join 3 table but in show duplicated result

2 Answers2