Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Allen Lamphear
Courses Plus Student 6,660 PointsPHP SQL Duplicate Result
I am having duplicate results.
What I am doing is joining 2 tables and then placing conditionals to show the results I need.
This is the result I get:
This is my PHP code:
<table class="table table-striped table-condensed table-hover">
<thead>
<tr>
<th>Chauff #</th>
<th>Name</th>
<th></th>
</tr>
</thead>
<tbody>
<?php
try {
$stmt = $pdo->prepare("SELECT test_profile.chauffid, test_profile.name, test_profile.Sunday, test_profile.Monday, test_profile.Tuesday, test_profile.Wednesday, test_profile.Thursday, test_profile.Friday, test_profile.Saturday, test_off.date
FROM test_profile LEFT OUTER JOIN test_off ON test_off.chauffid = test_profile.chauffid" );
$stmt->execute();
$result = $stmt->fetchAll();
if (count($result)) {
foreach ($result as $row) {
if($row['date'] == $id) {
// Show nothing
}elseif($row[$dayofweek] == 1){
echo '<tr">';
echo '<td class="employeeId">' . $row['chauffid'] . '</td>';
echo '<td class="name">' . $row['name'] . '</td>';
echo '<td><input type="checkbox" name="hasWork" class="hasWorkAv"/> Has Work</td>';
echo '</tr>';
}
}
} else {
echo "No One Is Considered As Available.";
}
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Database::disconnect();
?>
</tbody>
</table>
This is what the SQL generates:
As you can see it shows 2 results of John Doe. Which is fine but I only want to show 1 of him in the table. This is because he is showing on "Tuesday" (status = 1) meaning he is available and on the date he didn't request off for the date that is selected.
WHAT AM I OVERALL DOING?
I am joining 2 tables, Table 1 - test_profile (employee profile) Table 2 - test_off (employee request day off - Date of day off)
I am trying to see if someone has request a day off on the current date that is selected, if that date is selected and that person requested off, then do not show them available. If the person did not request off show them available.
If anyone needs more information please let know.
1 Answer
Ron McCranie
7,837 PointsIt looks like you're not limiting your results to a specific date. Since John Doe has 2 entries for days off (11-15 & 12-05) you're getting both results. You might be wanting to add in a WHERE clause that only returns entries from the 'test_off' table WHERE the date matches the date you provide.


Allen Lamphear
Courses Plus Student 6,660 PointsAllen Lamphear
Courses Plus Student 6,660 PointsRon McCranie , Thank you for your input. What the table is actually doing is just trying to see if the employee is available to work by his status of dayofweek. But I need an exception if he requested off on the specified date.
So say I select a date of 11/11/2014 and on that date the day of week is a Tuesday. I need to see in test_profile his status for Tuesday and then see if he requested off on that "date" in test_off table.
Make sense?