Solved

php mysql

Posted on 2012-12-31
12
500 Views
Last Modified: 2013-01-02
I have an idea about keeping track of employee hours on a database: this database will be populated by the normal hours the employees work: therefore if someone is late or out the supervisor only has to go and edit the data and saves time from having to put hours in each day for everyone.

I was able to populate the data of one person on a table, call it Hours_worked:

fields: name; emp_id; account_uid; norm_hours_worked; date.

I wrote up an edit page in php and everything works fine, only problem is I cannot populate the table with more than one employee. There must be a better way to do this although I want to keep if possible the idea of pre-populating the data. I can make a table for each employee but just looking for better way.
0
Comment
Question by:opelraja
12 Comments
 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 38734082
Hello opelraja,

I did not get your problem exactly, but in your table hours_worked you can store data of more than one employee without any problems. you should be able to identify unique employee data via emp_id field.

Please put some more detail about your problem.

Hope this will help you.

Thank you.

Amar Bardoliwala
0
 
LVL 9

Expert Comment

by:rinfo
ID: 38734153
Since normal work hours is fixed  make that available in a constant.
Just have a late hour table in which admin will input details of the employee who have come
late. lateid(auto generated id),EmpId,Date,latehours would be the field needed .
Of course you would need a run time script that would total late hours for a particular employee and deduct from the fixed normal hours constant to get the valid hours worked.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 167 total points
ID: 38734476
Make a Google search for this exact term: Should I Normalize My Database and read the very interesting ideas on both sides of the issue.  In your data model, you have two distinctly different things: People and Days.  These should not really be part of the same table.  The People (employees) have a one-to-many relationship with the days and work records.  Each row of the People table would represent a single employee.  A key for this employee would be stored in the rows of the table of Days.  Each row of the Days table would represent a single employee's work day (perhaps 200 per employee per year).  You would be able to join these tables for individual reporting and do arithmetic on the Days table for general statistics.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 167 total points
ID: 38734575
In your existing setup, as others pointed out you do not allow for record keeping. It also depends on what you are doing in the php.
Using an update that cumulatively adds hours to an employee.
Update table set hours=hours+$worked where employee_id=$id_from_web

You would need to reset hours at the beginning of the work week.  Issue will come up if you have irregular hours I.e. Worker works some hours in the current week and into the following week I.e. third shift from Sunday into Monday.
0
 
LVL 13

Assisted Solution

by:AielloJ
AielloJ earned 166 total points
ID: 38734622
opelraja:

Can you post the basic requirements of your application?  Do the number of hours vary by employee or calendar month?  Are the employees entering the data or the supervisor?  How long are you collecting the data for?

Please also take Ray's advice and get the basics of data normalization down.  As he pointed out, you have at least two distinct entities, and possibly more depending on your application's requirements.  An efficient data model will enable you to generate most of the information you need with relative ease.

There are several php open-source packages you may want to implement or use as a design guide, or starting point:

http://www.techmocracy.net/2007/01/04/php-open-source-timesheet-system/

Best regards,

AielloJ
0
 

Author Comment

by:opelraja
ID: 38734634
this is the sql I used to populate the dates: the rest of the data I put in as default values:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO emp_hours_worked2 (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2013-01-01','2013-12-31');

there are various employees with different times worked during the day, not all work 6 hours, some 5, some 4.

I do have a table for employee information which does have a field : emp_id and hours_per_day. I realize I can 'relate' that table to a few standard tables which describe the possible hours worked.

hours_table:

hour_id, hour_total, ... being some data like : 01, 6

a little background: there is already a new time keeping system going in that is used by phone which it appears will be like the last one we used and a complete catastrophe: so these records are backups for me and also a way for me to see how many extra hours I will have on the weekend to do 'extra' work using the hours not fulfilled during the week because of lateness or people being absent. I figured if I have the table filled, if I then sum is up for each person during the pay period I will know each day how many hours extra I will have to use and can therefore plan ahead by askng folks if they are available.

so the question is ' I guess' using the two tables(employees) and (hours_table) how to create a third table based on the two tables that will generate the data I want.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Expert Comment

by:arnold
ID: 38734675
You can use the phone system data to maintain your records with a requirement for a supervisor to confirm the hours reported within the phone system.
Your basic setup as was pointed out would be
Employee table
Employeeid, name, other info specific to employee.

Hours worked table
Employeeid,date,hours_worked,starttime,stoptime
You can use starttime and stoptime as date time to deal with cross day handling, the only thing to be caution deals with handling day light savings.
You would define the date if used or the combination of starttime and endtime along with the employeeid to make sure there is no record being added where it is not possible if users will be adding their entires or as part of the data import from the phone system
I.e. a new shift can not start if an existing one is ongoing.
Employee1,2013-01-01 12:00:00 GMT as starttime
Employee1,2013-01-01 15:30:00 GMT as starttime.
0
 

Author Comment

by:opelraja
ID: 38735253
I re-asked the question under 'sql query', 'mysql' and I ended up figuring out what I wanted to do: I will paste how I asked the question in the other post so it may be better understood what I wanted to do.

I have a table called employees: within it I have various fields two of which being:

emp_uid, and emp_shift_duration.

emp_uid is unique.

emp_shift_duration varies a bit becasue most emps work either 4 hours, 5 hours, 6 hours or 8 hours.

Sometimes people are out and I want to use their hours to do special projects so I want to keep tabs on when they miss hours. The shift sup. can keep track of this but in order to save time I only want him to 'edit' the missing hours when needed, not add hours eveyday so I want the table populated ahead of time.

so I created another table which is called :

emp_hours_worked: fields are 3 : emp_uid, projected_hours,and date.

I wish to populate emp_hours_worked with employee data from employees table.
emp_uid, and emp_shift_duration; and also populate date with the days of the year for 'each' employee: this will be a very long table because the result will be that each employee will have a record for each date of the year.: therefore using a simple php script the sup. can go in and edit any necessary dates and then I will run a query to sum up the hours for the current pay weeks.

emp_hours_worked will look something like this.

emp_uid,projected_hours,date

3432,4,01-01-2013
3432,4,01-02-2013
3432,4,01-03-2013...etc.

7715,8,01-01-2013
7715,8,01-02-2013...etc...  or ....

3432,4,01-01-2013
7715,8,01-01-2013
9824,5,01-01-2013
9954,8.01-01-2013... etc. depending how its shown but did this to get the idea through.

So I am looking for sql statement to 'insert' all this data into the emp_hours_worked table.


**** this is how I solved it below ****

I created a table 'dates with just '_dates' as a field : being the whole year day to day of 2013.

then I used this query:

Insert into emp_hours_worked(emp_uid,projected_hours,_date)
SELECT emp_uid, emp_shift_duration,_date
FROM employees_table AS a,
date AS b;

So it populated a line for each employee for each day. Now I just need a faster laptop for this huge table.
0
 

Author Comment

by:opelraja
ID: 38738953
I've requested that this question be closed as follows:

Accepted answer: 0 points for opelraja's comment #a38735253

for the following reason:

complete answer
0
 
LVL 76

Expert Comment

by:arnold
ID: 38738461
Your answer is basically a combination of all the suggestions and comments posted here when experts were trying to figure out what your setup is.
0
 

Author Comment

by:opelraja
ID: 38738946
Hi Arnold:  this was my problem or my question as stated in my original submission:

"only problem is I cannot populate the table with more than one employee" ,

 from all I saw in the answer/postings it was theory on how it could be done not on how to solve my problem, although some of the answers were helpful as theory if someone had the same problem I dont think they would solve it by reading the entries. I began with this original problem and really it was all about writing up the correct query which is why I re-wrote my problem and opened it under sql and mysql (only got one answer and actually just figured it out on the net). Anyways I have no problem doling out points for effort/contribution but I disagree that the posted comments collectively led to my answer. My resolution came by way of looking up a myriad of sql statements and working with examples until I got the result.  I will think out my question delivery better next time and be more exact. I would agree overall that the postings helped to some extent so I would like points spread out amongst the contributors. Probably the most helpful posting was Ray's.
0
 

Author Closing Comment

by:opelraja
ID: 38738954
thanks to all
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now