Welcome to WebmasterWorld Guest from 54.147.10.72

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)

5+ 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)

5+ 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 rocknbil is a WebmasterWorld Top Contributor of All Time 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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month