Query between 2 dates

PHP
1

Hi guys,

Ive got a date picker which passes 2 dates from a form to a PHP script. On that script i have a query for between those 2 dates. The problem i have is that to get todays entry (for example) i have to pick the end date as tomorrow.

$query = "SELECT * FROM clock_status , staff
WHERE auth = 1 and clock_status.user_id = staff.staff_number
and status = 0
and amend_time BETWEEN '$start_date' and '$end_date'";

The $start_date and $end_date are in date only format YYYY-MM-DD (2011-01-26) the date in the DB is YYYY-MM-DD hh:mm:ss.

Any suggestions how to include the whole day in the query.

thanks in advance.

2

when a query references more than one table, you should really qualify every single column reference

you didn’t, so forgive me if i guessed the wrong table

also, use explicit JOIN syntax, it’s muy bien

to answer your question, all you need to do is grab all the datetimes up to but not including midnight of the following day

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  FROM clock_status 
INNER
  JOIN staff 
    ON staff.staff_number = clock_status.user_id
 WHERE staff.auth = 1 
   AND staff.status = 0 
   [COLOR="Blue"]AND clock_status.amend_time >= '$start_date' 
   AND clock_status.amend_time  < '$end_date' + INTERVAL 1 DAY[/COLOR]
3

That makes sense, i will go over the code tomorrow and amend my queries as pointed out.

Thanks again.