Attendance

[shiyukoolest]

Need My Sql Php query in which

if for say date 1/1/09 emp no 1 first record is fetched from database it should be counted as In and second record for same date should be Out.  and time Duration between In and Out needs to be calculated.

 

Thanks a lot

[Daniel0]

Could you try rephrasing your post? This time with an actual question and proper English. No offense, but you post just doesn't make much sense.

[shiyukoolest]

yes sure.

 

I am developing an Attendance Management System.

I have the records in the database in this Format.

Emp No    Date/Time

1 2/6/2007 12:53

1 2/6/2007 14:20

1 2/6/2007 14:54

1 2/6/2007 19:58

1 2/6/2007 20:09

1 2/9/2007 8:32

1 2/9/2007 20:49

1 2/10/2007 8:17

 

I need to develop an Php MySql query in such a way that when it reads the record from the database,

When if for the date 2/6/2007 emp no 1, First record is considered as In time and the second record for the same date is considered as Out time.

 

Hope you got the question this time.

 

Thank you very much

 

[gizmola]

Your program is free to "consider" the data to be anything you want.  It seems that if you get a specific result set, which you haven't really specified in terms of your approach, and you were to fetch rows from that result set, you could then do your IN/OUT processing through alternation in a loop.  What you plan to do with those values is also unclear.

 

In simplest terms, if you were doing this through the entire database of these rows, then you'd want to at very least ORDER BY EmpNo, DateSlashTime.  This will default to ascending order, so you'll get the rows in the date/time order you desire.  The problem with these apps is always that if someone doesn't clock out, you have to do something logically to clock them out.  And of course this is also a design that won't handle people who might work a night shift that goes across days.

 

A much better design would be to have the clock table include an "EventType" column where it represents "In" and "Out" values.  Then you simply start with an "In' row, and look for the first corresponding "Out" row which has a datetime > than the "In" event.  This goes a long way towards makeing both problems I mentioned go away.

 

I also have an article I wrote that you might find helpful on the use of CURDATE() and NOW() in mysql for use in limiting date ranges in an "Aging" type of application, which is what this sounds like.

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

 

 

[shiyukoolest]

Hey thanks for your reply but I get these data in the Database from the Biometric reader and the raw data is in this format only.

 

So atleast i need to read these rows from the table and bifurcate them into In / Out

[gizmola]

Hey thanks for your reply but I get these data in the Database from the Biometric reader and the raw data is in this format only.

 

So atleast i need to read these rows from the table and bifurcate them into In / Out

 

Ok, but did you read the part where I talked about ORDER BY?

 

As I noted, a simple ORDER BY will sort the rows.  You can then fetch them in a loop using PHP.  First row in, 2nd row out, etc.