Data base making unauthorized changes - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Data base making unauthorized changes

What error am I making here

Baruch Menachem

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

10+ Year Member

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

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

WebmasterWorld Senior Member

10+ Year Member

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

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

10+ Year Member

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

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

WebmasterWorld Senior Member

10+ Year Member

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.