phpmysqlmysqli

How can I display client single data and not duplicate them

Please how can I display client single data and not duplicate them. As you can see on the result it duplicate each client twice.

and also clients in absent table if any of the client start date is equal to today's date it should not display the client until current date is greater than end date. Every other client can display except for those whose start date is same as current date.

Am trying to build a program that display clients who are not on annual leave.

When I run the code this is what I get

enter image description here

This is what I want

enter image description here

This are MySQL tables

First table

enter image description here

Second table

enter image description here

This is my code

<?php
    require_once 'db_connect.php';
    $varCurDate = date("Y-m-d");
    $sqlabs = "SELECT * FROM tbl_absent HAVING (startDate = '$varCurDate' AND endDate > '$varCurDate')";
    $resultabs = mysqli_query($conn, $sqlabs);
    if (mysqli_num_rows($resultabs) > 0) {
        while ($rowabs = mysqli_fetch_assoc($resultabs)) {
            $varSpecialId = $rowabs["specialId"];
            $varUserName = $rowabs["userName"];
            $varStartDate = $rowabs["startDate"];
            $varEndDate = $rowabs["endDate"];


            $sql = "SELECT * FROM tbl_clients WHERE (specialId != '$varSpecialId') GROUP BY specialId";
            $result = mysqli_query($conn, $sql);
            while ($row = mysqli_fetch_assoc($result)) {
                echo "
                    <tr>
                        <td>" . $row["userId"] . "</td>
                        <td>" . $row["userName"] . "</td>
                        <td>" . $row["userEmail"] . "</td>
                        <td>" . $row["userPhone"] . "</td>
                        <td>" . $row["specialId"] . "</td>
                    </tr>
                    ";
            }
        }
    } else {
        echo "0 results";
    }
    mysqli_close($conn);
    ?>
Solution

  • The problem is with your nested loops: The first query is selecting all absent staff, then the inner loop is selecting all that were not in the first list. To prevent this you should combine the queries to read all absent staff in one query, then loop through them like this:

    require_once 'db_connect.php';

$varCurDate = date("Y-m-d");

// Query to fetch all absent staff based on startDate and endDate
$sqlabs = "
    SELECT tbl_clients.userId, tbl_clients.userName, tbl_clients.userEmail, 
           tbl_clients.userPhone, tbl_clients.specialId 
    FROM tbl_clients 
    LEFT JOIN tbl_absent 
    ON tbl_clients.specialId = tbl_absent.specialId 
    WHERE tbl_absent.startDate = ? AND tbl_absent.endDate > ?
";

$resultabs = mysqli_execute_query($conn, $sqlabs, [$varCurDate, $varCurDate]);

if (mysqli_num_rows($resultabs) > 0) {
    while ($rowabs = mysqli_fetch_assoc($resultabs)) {
        echo "
            <tr>
                <td>" . $rowabs["userId"] . "</td>
                <td>" . $rowabs["userName"] . "</td>
                <td>" . $rowabs["userEmail"] . "</td>
                <td>" . $rowabs["userPhone"] . "</td>
                <td>" . $rowabs["specialId"] . "</td>
            </tr>
        ";
    }
} else {
    echo "No absent staff found.";
}