Date format in "where" clause
4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 2, 2013 (RSS)
By nmsinc - March 1, 2013
I have an issue with a date clause in a "where" statement no returning values:
----------------------------------------------------------------------------------------------------------
// get payroll date details
$dates1 = mysql_get('payroll_dates', 1);
$start = date("Y-m-d", strtotime($dates1['start_date']));
$end = date("Y-m-d", strtotime($dates1['end_date']));
elseif (!$CURRENT_USER['disabled']) {
$where = "employee_name = '".intval($CURRENT_USER['employee_number'])."' AND hours_worked > 0 AND date_hours_worked <= $end OR employee_name = '".intval($CURRENT_USER['employee_number'])."' AND hours_worked > 0 AND date_hours_worked >= $start ";
Any suggestions?
Thanks - nmsinc
By gregThomas - March 1, 2013
Hi,
It looks as if your MySQL syntax is correct. Is the where statement meant to be to one or the other of the two statements before and after the or?
If so I think the statement should look something like this:
$where = "(employee_name = '".intval($CURRENT_USER['employee_number'])."' AND hours_worked > 0 AND date_hours_worked <= $end ) OR ( employee_name = '".intval($CURRENT_USER['employee_number'])."' AND hours_worked > 0 AND date_hours_worked >= $start )";
The Brackets break up the statement into two separate MySQL statements, and all of the elements must be true in one or the other. I think in your current statement all of the elements must be true, and date_hours_worked can be less than $end or employee_name must bee equal to employee_number.
Thanks!
Greg
PHP Programmer - interactivetools.com
By nmsinc - March 1, 2013
Hi Greg,
I'm still getting all listings using you suggestion. Any other suggestions?
By nmsinc - March 2, 2013
I found my error - see correction below:
$where = ("employee_name = '".intval($CURRENT_USER['employee_number'])."' AND date_hours_worked BETWEEN '$start' AND '$end' ");
Works just fine now!