Hey Everyone,
A few days ago I posted a question about getting a timeclock database/web front end to behave properly and got great responses…I’m hoping to duplicate that with this issue:
Lunch Overages
I need to run a query that will pull when they left and when they got back (easy enough.) However, here’s where the water gets over my ears; I also need to then subtract the leave time from the arrive time for each user for each day THEN only return the results that are longer than 30 minutes.
Here’s the query that I’m running to pull the 411:
$lunch = "SELECT data.uid, data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName, users.uid
FROM data, users
WHERE
data.uid = users.uid
AND
data.date LIKE '%-$month-%'
AND
(
data.reason = 'lunch_leave'
OR
data.reason = 'lunch_return'
)
ORDER BY data.date, data.trutime";
the month is posted in from a form on the previous page. Not sure if a foreach php run or straight mysql loop will work to do what I need it to do, but any help will be greatly appreciated!
Thanks
Josh
This is relatively simple, just need to look at it a new way Can you do a ‘show create table’ for me so I can see how your dates / times are stored please?
Table Create Table
users CREATE TABLE users ( uid int(11) NOT NULL auto_increment, username varchar(60) NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, password varchar(32) NOT NULL,
PRIMARY KEY (uid),
KEY LastName (LastName)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
now for the data table:
CREATE TABLE data ( id int(11) NOT NULL auto_increment, UID int(11) NOT NULL, date varchar(11) NOT NULL, time varchar(20) NOT NULL, trutime time NOT NULL, reason varchar(25) NOT NULL,
PRIMARY KEY (id),
KEY reason_2 (reason),
KEY time_2 (time),
FULLTEXT KEY reason (reason),
FULLTEXT KEY time (time)
) ENGINE=MyISAM AUTO_INCREMENT=405 DEFAULT CHARSET=utf8
I’m thinking I’m about to expose my inexperience when I tell you this, but here it goes:
The date field is populated by php date() when the form is submitted as is time. trutime is the only true TIME field (go ahead, be amazed at my inventive naming structure.)
Date and time are then repopulated into a report. The trutime field is where I was planning to do the math with as it’s a true integer and the others are VARCHAR and Indexed for searching.
It’s ok, I’ve already derived that from the format of your field, but there’s many ways you can format a date in PHP, so i need to see an example of it populated.
Ideally you’d just run a single datetime field for anything, you can extract just the time or the date from the field if needed.
Perfect! You’ve got those formatted correctly. If it’s not too much work at this stage, you might want to create a test table and see what is like to use the date/time format on the table, you might be able to do an alter table without any loss (make sure you try on a TEST table first )
So another thing is that date, time are mysql commands, and they shouldn’t be used as column names I’d try to get to this format:
uid, reason, reason_date
where reason_date is your full date time but not in varchar.
From there you might write a query like such:
select
ll.uid, ll.reason_date as lunch_leave, lr.reason_date as lunch_return
from
(
/* Grab clock outs */
select
uid, reason, reason_date
from
myTable
where
reason = 'lunch_leave'
) ll
left join
(
/* Grab clock ins */
select
uid, reason, reason_date
from
myTable
where
reason = 'lunch_return'
) lr on ll.uid = lr.uid and
date(ll.reason_date) = date(lr.reason_date) //join by uid and date portion of reason date (brings in the lunch return for the same day of the lunch leave)
Now you have a view that can manipulated fairly easy, and you can run timediff() on the two new columns, and apply criteria to it
Going to have to spend some time with this one… I have never had success with AS. But I’m willing to give it a college try.
So you’re saying that I can pull date/time information out a simple datetime field and have it display how I want? I wouldn’t have to do some funky php trick to take it from 24hr time to 12hr time format?
Can you do a ‘show create table’ for me so I can see how your dates / times are stored please?