Q: Work out the amount of hours in last week I currently have a table of clocking logs which references two other tables. One table it references contains a log of clocking in's and the other a record of clocking out's: each has a unique ID.
For example (for each rwh_clock_in and rwh_clock_out table):
id 1
atTime 2018-06-05 14:01:38
userid 1

Then inside the clocking log (rwh_clocking):
id 1
clockInId 1
clockOutId 1
hours 8

I am trying to use the last tuesday strtotime('last tuesday') and the next tuesday strtotime('next tuesday') to work out the hours that employer has done during that week.
I am using PDO::Prepare(), the dates are placed in during the < > and then the user ID is placed in for which user I am calculating the hours during the most recent week for.
So far I have this:
SELECT * 
FROM rwh_clocking 
WHERE ( CASE WHEN cio.ci > ? 
        AND cio.co < ? ) 
        FROM ( SELECT ? AS id, 
             ( SELECT atTime FROM rwh_clock_in WHERE userid = id ) AS 'ci',
             ( SELECT atTime FROM rwh_clock_out WHERE userid = id ) AS 'co' ) cio;

However, this throws me an error in the CASE. Any help would be appreciated.
My desired output would be:
id clockInId clockOutId hours
1  1         1          8
2  2         2          4
etc..

When the clock_in and clock_out atTime is between last tuesday and next tuesday.
I have also tried:
SELECT clock.*, ( CASE WHEN cio.ci > ? AND cio.co < ? )
FROM rwh_clocking clock, ( SELECT ? AS 'id', 
    ( SELECT atTime FROM rwh_clock_in WHERE userid = id ) AS 'ci',
    ( SELECT atTime FROM rwh_clock_out WHERE userid = id ) AS 'co' ) cio;

I receive an SQL error: subquery returns more than 1 row when I run this:
SELECT c.clockInId, c.clockOutId, c.hours, cio.*
FROM rwh_clocking c,
    ( SELECT 1 as 'id',
        ( SELECT atTime FROM rwh_clock_in WHERE atTime > NOW() ) AS 'ci',
        ( SELECT atTime FROM rwh_clock_out WHERE atTime < NOW() ) AS 'co' ) cio;

So I believe that I need to cycle through each subquery before doing a CASE. Any ideas?
UPDATE:
SELECT c.clockInId, c.clockOutId, c.hours
FROM rwh_clocking c,
    ( SELECT 1 as 'id',
        ( SELECT atTime FROM rwh_clock_in WHERE atTime > NOW() - INTERVAL 5 MINUTE AND userid = id ) AS 'ci',
        ( SELECT atTime FROM rwh_clock_out WHERE atTime < NOW() AND userid = id ) AS 'co' ) cio;

Now returns me all the data but the times are not being taken into consideration.
*UPDATE:**
This now brings back no rows when they do exists.
SELECT c.clockInId, c.clockOutId, c.hours
FROM rwh_clocking c,
    ( SELECT 2 as 'id',
        ( SELECT id FROM rwh_clock_in WHERE atTime > NOW() - INTERVAL 60 MINUTE AND userid = id ) AS 'ci',
        ( SELECT id FROM rwh_clock_out WHERE atTime > NOW() + INTERVAL 5 MINUTE AND userid = id ) AS 'co' ) cio
WHERE c.clockInId = cio.ci AND c.clockOutId = cio.co


A: You don't need all those CASE expression and sub-queries. Use two joins (uses three named parameters userid, timein and timeout):
SELECT c.*
FROM
    rwh_clocking AS c
INNER JOIN rwh_clock_in AS ci
    ON c.clockInId = ci.id AND ci.userid = :userid
INNER JOIN rwh_clock_out AS co
    ON c.clockOutId = co.id AND co.userid = :userid
WHERE ci.atTime > :timein AND co.atTime < :timeout

Live fiddle
