Only the last record from the database is displaye

Question

I connected, I created a quick script in which I want to manage clients, domains and notes.

The problem is that when I add 2 notes to the client from ID: 1 - after viewing I see only one.

The following code shows what I have done so far

SQL Query:

$sql = "SELECT * FROM domain JOIN note ON domain.id = note.domain_id GROUP BY domain.id";

My PHP code:

while($rs = $resultdb->fetch_array(MYSQLI_ASSOC)) {
    echo '<tr>';
    echo '<td>'.$rs["id"].'</td>';
    echo '<td><strong><a href="'.$rs["domain_name"].'" target="_blank">'.$rs["domain_name"].'</a></strong></td>';
    echo '<td>'.$rs["note"].'</td>';
    echo '</tr>';
    }

The result he gets is:

ID   DOMAIN    NOTE

1    "domain1.com"  "note 1 to domain1.com"

2    "domain2.com"  "note 2 to domain2.com"

However, in the database I have added a few notes to domain1.com.

I would like to see all the notes added to a given domain.

EDIT:

When I do: "SELECT * FROM domain JOIN note ON domain.id = note.domain_id";

I getting:

I getting

I expect

EDIT: Add screnshot

LEFT JOIN

Answer 1

Your GROUP BY is limiting the records retrieved by the query. If you want all of the notes together you can try using GROUP_CONCAT() to produce a single field with all of the notes in one...

$sql = "SELECT domain.id as id, domain.domain_name as domain_name,
             GROUP_CONCAT(note.note) as note 
           FROM domain 
           LEFT JOIN note ON domain.id = note.domain_id 
           GROUP BY domain.id";

You might also change the JOIN to LEFT JOIN in case there are no notes for a particular domain.

Answer 2

you need group_concat group by note.domain_id

if you need exact match the use inner join

 "SELECT  id, domain, group_concat(note) as note 
    FROM domain 
    INNER JOIN note ON domain.id = note.domain_id
    GROUP BY note.domain_id";

if you needc result also for id without notes then try

 "SELECT  id, domain, (group_concat(ifnull(note,'')) as note 
    FROM domain 
    LEFT JOIN note ON domain.id = note.domain_id
    GROUP BY note.domain_id";

Answer 3

Probably you need to use a separate query to get "Domain Notes" in the while. for example:

while ($rs = $resultdb->fetch_array(MYSQLI_ASSOC)) {
    $sql_notes = "SELECT * FROM notes WHERE domain_id = '" . (int)$rs['domain_id'] . "'";

    ...
}