MySQL结果打印不止一个

Question

In my database I have a one-to-many table relationship where one parent can have many kids. The primary key is the parents email. I query to get the kids

$results1 = mysqli_query($con,"
SELECT directory.email
     , dirKids.kname
     , dirKids.kbirthday 
  FROM directory 
  JOIN dirKids 
    ON '$row[email]' = dirKids.parent
");

Then I loop through and echo the value to my html page

while($row1 = mysqli_fetch_array($results1)) {
    if (!empty($row1["kname"])) {
        echo "<tr><td>". $row1["kname"] ."</td><td>".
        $row1["kbirthday"]."</td></tr>";
    }
}

The problem I am having is that only one parent has kids in my database, but it will print the kids name and birthday 10 times because there are 10 people in my database. How can I get it to only print the child's name and birthday once?

My full code is listed below:

<?php
    $con = mysqli_connect("localhost", "username", "password", "db");
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $results = mysqli_query($con,"SELECT directory.id, directory.fname, directory.lname, directory.address, directory.bdname, directory.birthday, directory.cell, directory.email, directory.sFName, directory.sBirthday, directory.sCell, directory.sEmail FROM directory ORDER BY lname") or die ("couldn't fetch query");

    echo "<div class='accordion' id='accordion'>";


    // output data of each row
    while($row = mysqli_fetch_array($results)) {

    $results1 = mysqli_query($con,"SELECT directory.email, dirKids.kname, dirKids.kbirthday FROM directory JOIN dirKids ON '$row[email]' = dirKids.parent");

    echo "</table></div>";
    if ($row['sFName'] == "" || $row['sFName'] == "undefined") {
        echo "<div class='card'><div class='card-header'
        id='headingOne'><h5 class='mb-0'><button class='btn btn-link' 
        type='button' data-toggle='collapse' data-target='#collapse".
        $row["id"] ."' aria-expanded='true' aria-controls='collapse".
        $row["id"] . "'><h5>".$row["fname"] ."<span id='lnameText'>".
        $row["lname"] ."</span></h5></button></h5></div><div
        id='collapse". $row["id"] . "' class='collapse'
        aria-labelledby='headingOne' data-parent='#accordion'><div
        class='card-body'><table id='myUL' class='table'><tr></tr><tr>
        <td><h5>Address</h5></td><td>". $row["address"] ."</td></tr>
        <tr><td><h5>Birthday</h5></td><td>".$row["birthday"]."</td>
        </tr><tr><td><h5>Cell</h5></td><td>". $row["cell"]."</td></tr>
        <tr><td><h5>Email</h5></td><td>". $row["email"] ."</td></tr>
        </table></div>";

    echo "<div class='col-md-6'><h3>Children</h3><table class='table'><tr><th><h5>Name</h5></th><th><h5>Birthday</h5></th>";

    while($row1 = mysqli_fetch_array($results1)) {
        if (!empty($row1["kname"])) {
            echo "<tr><td>". $row1["kname"] ."</td><td>". $row1["kbirthday"]."</td></tr>";
        }
    }

    echo "</table></div></div>";
?>

Answer 1

Since the data needed for the second while loop comes exclusively from the kids table, just build your SELECT statement for that, forget the join and the WHERE statement looks for only the parents email.

The below code goes inside the primary while loop and replaces the

$results1 = mysqli_query($con,"SELECT directory.email, dirKids.kname, dirKids.kbirthday FROM directory JOIN dirKids ON '$row[email]' = dirKids.parent");

with

//Build the select statement
$sql = "SELECT kname, kbirthday FROM dirKids WHERE parent = '" .$row[email] . "'";
//now run the query
$results1 = mysqli_query($con,$sql);
//uncomment the below to see the results
//var_dump(mysqli_fetch_array($results1));