php mysql

Posted on 2012-12-31
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.
Question by:opelraja
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Expert Comment

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.
LVL 110

Accepted Solution

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.
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

LVL 78

Assisted Solution

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.
LVL 13

Assisted Solution

AielloJ earned 166 total points
ID: 38734622

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:

Best regards,


Author Comment

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

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
  WHILE dateStart <= dateEnd DO
    INSERT INTO emp_hours_worked2 (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
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.


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.
LVL 78

Expert Comment

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
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.

Author Comment

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.



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

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.

Author Comment

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
LVL 78

Expert Comment

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.

Author Comment

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.

Author Closing Comment

ID: 38738954
thanks to all

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

726 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