homepage Welcome to WebmasterWorld Guest from 54.163.91.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Data base making unauthorized changes
What error am I making here
Baruch Menachem




msg:4416039
 7:33 pm on Feb 9, 2012 (gmt 0)

I want to make a little time card application. This is the start of the preliminary testing, which is already going a bit wrong


create table timesheet(
record_no int(12) auto_increment primary key,
emp_no int(6),
punch_in timestamp,
punch_out timestamp)

insert into timesheet(empno, punch_in) values (86,now());

mysql> select * from timesheet;
+-----------+--------+---------------------+---------------------+
| record_no | emp_no | punch_in | punch_out |
+-----------+--------+---------------------+---------------------+
| 1 | 86 | 2012-02-09 10:15:25 | 0000-00-00 00:00:00 |
| 2 | 99 | 2012-02-09 10:15:48 | 0000-00-00 00:00:00 |
+-----------+--------+---------------------+---------------------+



So far so good. now the next step, which creates problems


insert into timesheet(emp_no,punch_in) values (13,now());
mysql> update timesheet set punch_out=now() where emp_no=86;
select * from timesheet;
+-----------+--------+---------------------+---------------------+
| record_no | emp_no | punch_in | punch_out |
+-----------+--------+---------------------+---------------------+
| 1 | 86 | 2012-02-09 10:43:17 | 2012-02-09 10:43:17 |
| 2 | 99 | 2012-02-09 10:15:48 | 0000-00-00 00:00:00 |
| 3 | 13 | 2012-02-09 10:19:21 | 0000-00-00 00:00:00 |
+-----------+--------+---------------------+---------------------+



Why does the call to update punch_out also update the value to punch in?

 

Dijkgraaf




msg:4416118
 1:27 am on Feb 10, 2012 (gmt 0)

After creating the table with your script and then exporting the definition it is as follows.

CREATE TABLE IF NOT EXISTS `timesheet` (
`record_no` int(12) NOT NULL auto_increment,
`emp_no` int(6) default NULL,
`punch_in` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`punch_out` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`record_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


What you want is
CREATE TABLE IF NOT EXISTS `timesheet` (
`record_no` int(12) NOT NULL auto_increment,
`emp_no` int(6) default NULL,
`punch_in` timestamp NOT NULL default CURRENT_TIMESTAMP,
`punch_out` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`record_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

See the difference?
I looks like if you don't set the default that for the first time stamp it not only sets the default, it also sets on update CURRENT_TIMESTAMP as well.

Baruch Menachem




msg:4416299
 4:07 pm on Feb 10, 2012 (gmt 0)

Thanks.

Now for the next problem.....

The machine does odd math with the time stamps. Is this just not a good tool for this purpose?
update timesheet set worked_hours=
sec_to_time(
time_to_sec(punch_out)-
time_to_sec(punch_in)
)where record_no in (1,2)

mysql> select * from timesheet;
+-----------+--------+---------------------+---------------------+--------------+
| record_no | emp_no | punch_in | punch_out | worked_hours |
+-----------+--------+---------------------+---------------------+--------------+
| 1 | 86 | 2012-02-09 19:53:52 | 2012-02-09 20:18:43 | 00:24:51 |
| 2 | 99 | 2012-02-09 19:54:04 | 2012-02-10 07:30:49 | -12:23:15 |
| 3 | 13 | 2012-02-09 19:54:26 | 0000-00-00 00:00:00 | NULL |
| 4 | 76 | 2012-02-09 19:57:50 | 0000-00-00 00:00:00 | NULL |
+-----------+--------+---------------------+---------------------+--------------+

Thanks again

rocknbil




msg:4416322
 4:54 pm on Feb 10, 2012 (gmt 0)

you'd probably want to do that using timediff() [dev.mysql.com] or some other mysql function. Reason being . . . what about the graveyard shift or late night redeye projects? Start and end might be on different days.

update timesheet set worked_hours=timediff(punch_out-punch_in) where record_no in (1,2)

you might add formatting to round it to the nearest second, but that's a good start.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved