LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 04-21-2005, 03:25 PM   #1
ezra143
Member
 
Registered: Aug 2003
Location: NY
Distribution: RH9, RH8, Slack, Vector
Posts: 497

Rep: Reputation: 31
mySQL query help


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
 
Old 04-21-2005, 04:48 PM   #2
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 67
Re: mySQL query help

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.
 
Old 04-22-2005, 09:12 AM   #3
ezra143
Member
 
Registered: Aug 2003
Location: NY
Distribution: RH9, RH8, Slack, Vector
Posts: 497

Original Poster
Rep: Reputation: 31
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.
 
Old 04-25-2005, 03:17 PM   #4
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 67
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need mysql query help DropHit Linux - Software 7 04-01-2005 11:32 AM
MySQL++ Query jimbob8483 Programming 1 12-15-2004 06:47 AM
MySQL Query Help Gerardoj Programming 1 06-10-2004 09:54 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 12:27 PM
mysql query !!! hitesh_linux Linux - General 1 02-03-2003 03:36 AM


All times are GMT -5. The time now is 07:24 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration