mySQL query help

ezra143 · 04-21-2005, 03:25 PM

Code:

$query = "SELECT DATE_SUB(CURDATE(),INTERVAL 3 DAY)";
$date = MYSQL_QUERY($query) or die("SQL Error Occured : ".mysql_error().':'.$query);
$mysqlquery = "SELECT timesheet.empid, MAX(timesheet.checkin) as 'lastcheckin', employee.lastname, employee.firstname FROM timesheet INNER JOIN employee ON timesheet.empid=employee.empid WHERE 'lastcheckin' < '$date' AND employee.active='y'  AND employee.admin='0' GROUP BY empid ORDER BY 'lastcheckin'";
$queryresult = MYSQL_QUERY($mysqlquery) or die("SQL Error Occured : ".mysql_error().':'.$mysqlquery);

What am I doing wrong?

I want it to return timesheet.checkin where it is older than today -3 days.

TIA

rjlee · 04-21-2005, 04:48 PM

Quote:

Originally posted by ezra143

Code:

$query = "SELECT DATE_SUB(CURDATE(),INTERVAL 3 DAY)"; $date = MYSQL_QUERY($query) or die("SQL Error Occured : ".mysql_error().':'.$query); $mysqlquery = "SELECT timesheet.empid, MAX(timesheet.checkin) as 'lastcheckin', employee.lastname, employee.firstname FROM timesheet INNER JOIN employee ON timesheet.empid=employee.empid WHERE 'lastcheckin' < '$date' AND employee.active='y' AND employee.admin='0' GROUP BY empid ORDER BY 'lastcheckin'"; $queryresult = MYSQL_QUERY($mysqlquery) or die("SQL Error Occured : ".mysql_error().':'.$mysqlquery);

What am I doing wrong?

I want it to return timesheet.checkin where it is older than today -3 days.

TIA

First of all, 'lastcheckin' is a literal string, i.e. data. If you want to quote a column name, you have to use double quotes.

Secondly, you are including $date in the string without quoting it. Depending on the database schema, this may be a bad idea; you should probably use a parameterised query here. It's good practice to always use parameterised queries to include variables in SQL, and if that's not possible for some reason, use DBI::quote to quote the string, rather than just putting quotes around it.

Thirdly, this is only a program fragment. You haven't defined mysql_error()or MYSQL_QUERY() anywhere, so we don't actually know the calling semantics of the routines you're using.

ezra143 · 04-22-2005, 09:12 AM

Ok, I double qouted as you said, and I still get the same output. I found a problem in the data types.

SELECT DATE_SUB(CURDATE(),INTERVAL 3 DAY) outputs in the format yyyymmdd

without the DATE_SUB it would be yyyy-mm-dd because DATE_SUB seems to change it to a numeric context.

where 'lastcheckin' is set by a NOW() function so it is in the format yyyy-mm-dd hh:mm:ss

how can I make the two work together? Is there an easy way to modify NOW() and maintain the format for mySQL to compare the dates? I dont really care about the hours for the purpose of the query, it is just looking at absenteeism of more than 2 days +/-.

This is probably fairly easy to do, but I'm relatively new to any type of programming.

Thanks again,

BTW: This is in PHP.

rjlee · 04-25-2005, 03:17 PM

Quote:

Originally posted by ezra143
Ok, I double qouted as you said, and I still get the same output. I found a problem in the data types.

SELECT DATE_SUB(CURDATE(),INTERVAL 3 DAY) outputs in the format yyyymmdd

without the DATE_SUB it would be yyyy-mm-dd because DATE_SUB seems to change it to a numeric context.

It's not supposed to; see the examples under DATE_ADD/DATE_SUB on http://dev.mysql.com/doc/mysql/en/da...functions.html

This may be something to do with your date display settings?

Quote:

where 'lastcheckin' is set by a NOW() function so it is in the format yyyy-mm-dd hh:mm:ss

how can I make the two work together? Is there an easy way to modify NOW() and maintain the format for mySQL to compare the dates? I dont really care about the hours for the purpose of the query, it is just looking at absenteeism of more than 2 days +/-.

Look at the DATE_FORMAT function on the same link.