Fetch data that don't exist in a table with a specific id from another table

Question

I'm new to MySQL and PHP and I need your help. I have made a web application for the check-ins that employees make at the company. I have an SQL table named tblemployees that has the employees' emp_id, Name, Email ID etc. The other table is named tblentries and has all the entries of each employee and variables such as id. emp_id, Name, Date, Hour etc. The primary key of tblemployees is emp_id and emp_id is the foreign key of the tblentries table. I want to make a table in my web app that shows the pending checks of the day. To be more specific, my code so far is:

<div class = "pb-20" id = "tab">
                    <table class = "data-table table stripe hover nowrap">
                        <thead>
                            <tr>
                                <th class = "table-plus">Full Name</th>
                                <th>Email</th>
                                <th>1st Comp.</th>
                                <th>2nd</th>
                                <th>3rd</th>
                                <th>4th</th>
                                <th>5th</th>
                                <th>Department</th>
                                <th>Position</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <?php 
                                    $sql = "SELECT tblemployees.Name, tblemployees.EmailId, tblemployees.Company1, tblemployees.Company2, tblemployees.Company3, tblemployees.Company4, tblemployees.Company5, tblemployees.Department, tblemployees.role FROM tblemployees LEFT JOIN tblentries ON tblentries.Date < '$todaysdate'";
                                    $query = mysqli_query($conn, $sql) or die(mysqli_error());
                                    while ($row = mysqli_fetch_array($query)) {
                                ?>  
                                <td class = "table-plus">
                                    <div class = "name-avatar d-flex align-items-center">
                                        <div class = "txt">
                                            <div class = "weight-600"><?php echo $row['Name']; ?></div>
                                        </div>
                                    </div>
                                </td>
                                <td><?php echo $row['EmailId']; ?></td>
                                <td><?php echo $row['Company1']; ?></td>
                                <td><?php echo $row['Company2']; ?></td>
                                <td><?php echo $row['Company3']; ?></td>
                                <td><?php echo $row['Company4']; ?></td>
                                <td><?php echo $row['Company5']; ?></td>
                                <td><?php echo $row['Department']; ?></td>
                                <td><?php echo $row['role']; ?></td>
                            </tr>
                            <?php } ?>  
                        </tbody>
                    </table>
                </div>

*'$todaysdate' is a variable that stores today's date. It has the same format as tblentries.Date.

Whatever I have tried so far I haven't managed to show the right results. I have made some entries for testing the app and the table that I want to make shows the names of the employees whose entries I have made for testing. (The test entries have made in the past, so their dates are smaller than $todaysdate. I don't know which is better to use and how Not In, Not Exists or a Left Join? Thank you :)

Solve the first step only - obtain needed data on pure SQL without PHP and HTML. — Akina, Mar 30 '22 at 07:13
Most of this code is irrelevant I think. See [ask] and how to create a [mre] of your issue. It's far easier it you show us the data rather than just describing it. Show (a sample of) the SQL data you're working with, the SQL query, the expected result you got, and the result you wanted instead. All as text within the question please. You can run the SQL query separately (e.g. in phpmyAdmin or MySQL Workbench) to test it before you integrate it into the PHP. — ADyson, Mar 30 '22 at 08:00
`or die(mysqli_error());`...guessing you got this from a bad tutorial or just made a mistake. It'll never work like that because mysqli_error (as per the documentation) expects you to pass in a connection object. But anyway there are far better ways to handle mysqli errors - read [mysqli or die, does it have to die?](https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die) to learn more. — ADyson, Mar 30 '22 at 08:05
If you just want to show entries for today's date, then why are you using `<` (less than)? `=` would make more sense. Also if it's always showing today's date then you don't need to inject that value into the SQL from PHP, you can just write `WHERE tblentries.Date = CURDATE()`. That's assuming `Date` is a `date` or `datetime` column in the table (which it should be - don't ever store dates in varchar fields). — ADyson, Mar 30 '22 at 08:07
Also your LEFT JOIN clause doesn't make sense. You need to join the two tables together using a common ID so that it associates the time entries with the relevant person. — ADyson, Mar 30 '22 at 08:09
From what you've said I think the query should probably be `SELECT tblemployees.Name, tblemployees.EmailId, tblemployees.Company1, tblemployees.Company2, tblemployees.Company3, tblemployees.Company4, tblemployees.Company5, tblemployees.Department, tblemployees.role FROM tblemployees LEFT JOIN tblentries ON tblEmployees.emp_id = tblEntries.emp_id WHERE tblentries.Date = CURDATE()`. But it'll be a lot easier to be certain if you supply the examples I mentioned above. — ADyson, Mar 30 '22 at 08:09
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) 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/32391315) — Dharman, Mar 30 '22 at 10:15

score 1 · Answer 1 · edited Mar 31 '22 at 11:21

Found the solution. I used WHERE NOT EXISTS(), so as to fetch the names etc of those who belong to tblemployees table and do not have an entry for CURATE() in tblentries table :

$sql = "SELECT DISTINCT tblemployees.Name, tblemployees.EmailId, tblemployees.Company1, tblemployees.Company2, tblemployees.Company3, tblemployees.Company4, tblemployees.Company5, tblemployees.Department, tblemployees.role 
FROM tblemployees 
WHERE NOT EXISTS ( SELECT DISTINCT tblentries.emp_id, tblentries.Date FROM tblentries WHERE tblentries.emp_id = tblemployees.emp_id AND tblentries.Date = '$todaysdate')";

Fetch data that don't exist in a table with a specific id from another table

1 Answers1