Working with times...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Czar
Forum Commoner
Posts: 58
Joined: Sun Dec 29, 2002 11:17 am

Working with times...

Post by Czar »

I have an employee database, which logs when employee enters work and when he/she leaves work. Times are in YYYY-MM-DD HH:MM:SS form. Now i am trying to work out a script that pulls out weekly hours of employee.

Code: Select all

while($row_getReport = mysql_fetch_assoc($getReport)) {

			$week_hours_row = "SELECT ((UNIX_TIMESTAMP( ma1 ) - UNIX_TIMESTAMP( ma2 )) / 3600),
			((UNIX_TIMESTAMP( ti1 ) - UNIX_TIMESTAMP( ti2 )) / 3600),((UNIX_TIMESTAMP( ke1 ) - UNIX_TIMESTAMP( ke2 )) / 3600),
			((UNIX_TIMESTAMP( to1 ) - UNIX_TIMESTAMP( to2 )) / 3600),((UNIX_TIMESTAMP( pe1 ) - UNIX_TIMESTAMP( pe2 )) / 3600),
			((UNIX_TIMESTAMP( la1 ) - UNIX_TIMESTAMP( la2 )) / 3600),((UNIX_TIMESTAMP( su1 ) - UNIX_TIMESTAMP( su2 )) / 3600) AS hrs FROM workhours";
			mysql_select_db($database_gntdb, $gntdb) or die(mysql_error());
			$hours_in_week = mysql_query($week_hours_row) or die(mysql_error());
			$get_hours_in_week = mysql_fetch_array($hours_in_week) or die(mysql_error());
This one gets the difference of coming and leaving in decimal hours. Then i make some math to solve out if if employee gets extra 0,5 hours per day.

Code: Select all

$ma = abs($get_hours_in_weekї'0']);
			if($ma > '7.5' && $ma < '8.0') &#123; $ma_VKY = '8.0'-$ma; &#125; elseif($ma > '8.0') &#123; $ma_VKY = '0.5'; &#125; else &#123; $ma_VKY = '0.0'; &#125;
...and so on for each day.

next the script prints out the values and closes while loop.

The prob is this: Its works out fine with ONE entry in DB, but of course with multiple entries (the way it goes...) those figures won't be realistic, due to multiple instances of same variables ( because of the while loop ).

For example, if i have two entries in DB, the first one has worked from monday to thursday, but the other one only from monday to tuesday, the print screws up like this:

Name: John Doe
MON: 8.75 VKY: 0.5
TUE: 8.97 VKY: 0.5
WED: 9.07 VKY: 0.5
THU: 5.5 VKY: 0.0
FRI: 0 VKY: 0.0
SAT: 0 VKY: 0.0
SUN: 0 VKY: 0.0
WEEK: 32.29 1.5

Name: Jane Doe
MON: 8.75 VKY: 0.5
TUE: 8.97 VKY: 0.5
WED: 9.07 VKY: 0.5
THU: 5.5 VKY: 0.0
FRI: 0 VKY: 0.0
SAT: 0 VKY: 0.0
SUN: 0 VKY: 0.0
WEEK: 32.29 1.5

U see what i mean? Each row is different in database, but the print is like this...

Anyone? Anything?
Czar
Forum Commoner
Posts: 58
Joined: Sun Dec 29, 2002 11:17 am

Post by Czar »

up...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sorry I forgot about this thread.. :oops:

could you post the table structure (create table and all) and some dump data, so I can play around with it?
Czar
Forum Commoner
Posts: 58
Joined: Sun Dec 29, 2002 11:17 am

Post by Czar »

Table structure:

Code: Select all

CREATE TABLE `workhours` (
  `id` int(10) NOT NULL auto_increment,
  `kpa` char(3) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `viikko` int(2) NOT NULL default '0',
  `ma1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ma2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ma_s` varchar(25) NOT NULL default '',
  `ti1` datetime default '0000-00-00 00:00:00',
  `ti2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ti_s` varchar(25) NOT NULL default '',
  `ke1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ke2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ke_s` varchar(25) NOT NULL default '',
  `to1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `to2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `to_s` varchar(25) NOT NULL default '',
  `pe1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `pe2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `pe_s` varchar(25) NOT NULL default '',
  `la1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `la2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `la_s` varchar(25) NOT NULL default '',
  `su1` timestamp NOT NULL default '0000-00-00 00:00:00',
  `su2` timestamp NOT NULL default '0000-00-00 00:00:00',
  `su_s` varchar(25) NOT NULL default '',
  `comments` varchar(200) default NULL,
  `shift` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
And this is how data goes into the table:

Code: Select all

INSERT INTO `workhours` VALUES (7, '402', 'John Doe', 2, 0x323030352d30312d31302030383a30303a3030, 0x323030352d30312d31302031363a34353a3030, '1', '2005-01-11 08:44:00', 0x323030352d30312d31312031373a34323a3030, '1', 0x323030352d30312d31322030393a31303a3030, 0x323030352d30312d31322031383a31343a3030, '1', 0x323030352d30312d31332030373a30303a3030, 0x323030352d30312d31332031323a33303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', 0x303030302d30302d30302030303a30303a3030, 0x303030302d30302d30302030303a30303a3030, '1', NULL, 1);
etc...
Post Reply