Compare Value in Mysql - using count arguement inside the query

What I was trying to achieve here was to be able to check for duplication of images.   In this scenario I'm looking for  users 'Employee'  who have been put down as doing 2 shifts at exactly the same time .   Ideally I want the mysql query to be doing as much work as possible.

For this task I'm running 2 queries in total . 

1.  the first one makes a count of employees who have shifts on the same day.
2. Secondly to run through those employees and see if there shifts clash.

I'm pretty sure that there might be a one query solution here somewhere and when I get a chance I may well come back to this and have a look.  Programming can be a bit like playing Soduko sometimes and you have to stare at the problem for a while before it comes to you.

Anyway to those two queries, the trick in the first one is using HAVING over using WHERE enabling me to use COUNT as a WHERE clause inside the mysql query.   Take a look at this example.


$query  = "select ";
      $query .= "id, user_id, cdate, start_time, end_time, count(*) ";
      $query .= "from #__pf_time_tracking ";
    $query .= "group by user_id, cdate having count(*) >1 ";

This returns to me the rows of all Employees that have more than one shift on that day.

Then with those rows I iterate through them to see if there is a time clash.  The trick here using BETWEEN as the comparison operator.   It calculates whether the shift being iterated through has at any time in the shift got a time that collides with the start time of the shift we are checking in the MYSQL query.

         $query  = "select ";
      $query .= "tt.project_id, tt.start_time, tt.end_time, cp.user_id, cp.firstname, cp.lastname, pj.title, pj.location ";
      $query .= "from #__pf_time_tracking as tt ";
        $query .= "LEFT JOIN (#__comprofiler as cp, #__pf_projects as pj) ON (tt.user_id=cp.user_id AND tt.project_id=pj.id) ";
    $query .= "WHERE tt.user_id='$user' AND tt.cdate='$date' AND tt.start_time BETWEEN '$start_time' AND '$end_time' AND tt.id != '$shift_id' ";
        

Please let me know if you think you know of any improvements to this logic.