Concept Grasping - Simple employee checkin/out application

[Noumes]

Hi everyone,

Long story short: getting tired of reading php and mysql web dev. From the start I knew I was a hands on learner (as with css and html) it came natural once I began working on items so I decided instead of just flipping threw long pages of words I'd attempt to create what I want through trying (and well apparently asking for help)

 

The application is a simple one:

 

The screen displays the current time and a form. type in your id (ex: 1234) into the form and click 'Check in'. A link on the page links to what is essentially users.php and lists employees who are currently checked into the system showing: Time checked in: & Name: (so 11:00 - John) and next to their name a link to 'Check out'.

 

Now all the check in's and out's would be saved into the mysql database, and through an admin page could be retrieved and sorted by date ( to print user times on payday)

 

Here's my problem....

I haven't a clue what the correct way to set this up would be.

 

What I am thinking: Two databases: 1 called employees' and one called worktimes

 

employees would store the employee user info:

rank (to allow or restrict access to the admin page - simple number value 1 or 2),

ID (what is entered on main screen to 'check in' to work)

Name (their name)

 

Worktimes would store:

date

check in time

check out time

employee id (to tell who checked in)

 

really this is all I can think of but to me it already seems unfuctional. for some reason I keep thinking that storing the login/out would work better in the user database instead of in its own and my head is already aching.

 

Any guidance is greatly appreciated, and if you know how you'd set this up PLEASE I'd be grateful to read through it!

 

thanks for taking your own time to even read this.

 

[Q]

Hi Noumes

 

The thinking you've done are GREAT! - Exactly the way I'd have done it.

 

Except one thing:

Worktimes would store:
check in time (unix_timestamp)
check out time (unix_timestamp)
employee id (to tell who checked in)

 

So firstly make your index.php with the form and time.

 

Best of luck!

[Noumes]

Thanks for the quick reply and help. I'm posting at night since I've been reading all day and planning the better part of the last hour. I'm going to sleep in a bit but I have this post on email notification and will keep watch. Anyone is welcome to post the more ideas the more concepts to learn (which im dying to do).

 

I'll start first thing tomorrow setting up a 'sandbox' of some sort and laying out what I can recall off memory from the book. (db connect page, admin page, index.php, and the tables)

 

 

[Q]

That sounds great! - I'm posting during daytime. It's almost midday where I am, in India

[Noumes]

Born in Pakistan myself, would be posting besides you if I hadn't moved to the U.S

 

 

[Noumes]

Here's my form:

			<form method="post" action="login.php" id="test" name="test">
			<input type="text" name="code" value="code" />
			<input type="submit" value="Check In" id="submit" name="submit" />
		</form>

 

and here's my login.php page:

 

<?php

if(isset($_POST['submit'])) {
  $code = $_POST['code'];


$mysql = new mysqli('localhost','root','','arco') or die('There was a problem connecting to the database');
if($stmt = $mysql->prepare('INSERT INTO worktimes VALUES (NULL,NULL,?)')) {
  $stmt->bind_param('s',$code);
  $stmt->execute();
  $stmt->close();
} else {
  echo 'error: ' . $mysql->error;
}
}

?>

 

and my databases:

name = their name

code = login code (ex: 1234)

loggedin = 0 or 1, 1 means logged in. This is to display a list of currently logged in users.

CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `code` int(11) NOT NULL,
  `loggedin` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

 

CREATE TABLE IF NOT EXISTS `worktimes` (
  `checkin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `checkout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `code` int(11) NOT NULL
)

 

here's my problem, after I did that, I wanted to see how the timestamps worked. I figured automatically and then it hit me. if its automatic, won't checkin and checkout be the same? and I was right.

 

after loggin in here were the results:

checkin:2009-07-14 16:37:14 checkout:2009-07-14 16:37:14 code:1996

 

another issue I noticed, was how do I check to see if the code entered is a valid one before writing to the worktimes table? Meaning how to I check the input against the employees table before inserting the employee code into worktimes.

 

thanks.

[xtopolis]

I think you are making this more complicated than necessary.  My basic approach would be to start with a database design like this:

 

[pre][employees]

empid(int,auto)  | firstname(varchar) | lastname(varchar)

-----------------------------------

  1    | bob      | finlid

  2    | steve    | perry

  3    | sam      | daman

 

[employee_groups]

empid(int)  | groupid(int)

------------------

  1    | 1

  2    | 2

  3    | 2

 

[groups]

groupid(int,auto) | groupname(varchar)

--------------------

  1    | Manager

  2    | Employee

 

[timetable]

timeid(int,auto)  |  empid(int) | when(datetime)        |  eventtype(int)

----------------------------------------------

  ..

  46              |      1      | 2009-07-14 08:30:39  | 0

  47              |      2      | 2009-07-14 08:34:21  | 0

  ..

 

  57              |      2      | 2009-07-14 16:01:20  | 1

[/pre]

 

Break the tables down into their parts and only record what's relevant.  You don't want to use any on update current timestamp in my opinion.  I can't think of the query off the top of my head, but if you have the start time, end time, and also in between times (lunch breaks?) you could find out how many hours were worked, etc.

 

If you notice in the last table design, I use the eventtype column.  This could have its own relational table holding those types I mentioned: start, end, lunch(start), lunch(end) etc.  You can also add more easily.

 

The same goes for the user type.  I have groups that users can be a part of managers, employees... and then you can allow those GROUPS to edit or not edit, etc.

 

I strongly suggest you draw out this on paper first, thinking clearly about what functionality it needs to have, and what formulas it will use to calculate which values.  Lay it all out on paper so you can get an idea of how it might fit together.  Don't forget to account for human errors: (not clocking out), (daylight savings time if available), holidays, etc...

[Noumes]

@xtopolis, I really want to thank you. More than getting this to work first try I want to build a correct understanding and method on how to approach tasks, and for you taking time to show a very detailed method on how you'd properly do it is beyond thanking.

 

My understanding of what your layout is:

 

Employees table:

- ID: unique ID for all employees

- Names - self explanatory.

 

Employee_groups:

- Setting each employee to a group. (could this be another row under employees?)

 

groups

- setting what exactly the groups are. (giving meaning to 1 and 2)

 

Timetable is where I get confused.

eventtype would be like 1 = signin, 2 = signout, 3= 30min(standard) break?

I can't wrap my head around this table for some reason, but I'm going to continue evaluating it. If you have the time It would be greatly appreciated if you could briefly walk me through that table.

 

If it is assigning a value to the events, I can see a use already for possibly listing break times of employees.

 

One thing that I do need however is the employee 'code'. this code would basically correspond to the code they already use to login to the cash register, and it would be the exact same # (four digit) to login.

 

thanks again for taking your time to not just write your reply, but to evaluate my previous posts and logic.

 

[Noumes]

Before reading through, I've uploaded the current version of what I talk about below at:

http://mildgreen.com/projects/learnphp/arco.rar

this may be easier or not to view the code on your own localhost server for convenience.

 

I have tried to implement what you suggested. The current problem: Won't write values into database.

 

(i've stripped out my html tags like head and the unnecessary div's)

<?php

if(isset($_POST['submit'])) {
$templogin = $_POST['emplogin'];
$eventtype = 1;
$correctlogin = 'no';
}

$mysql = new mysqli('localhost','root','','arco') or die('There was a problem connecting to the database');
$stmt = $mysql->prepare('SELECT empid,emplogin FROM employees');
$stmt->execute();
$stmt->bind_result($empid,$emplogin);
?>

<body>


	<?php while($row = $stmt->fetch()) : ?>
<?php

	if ( $templogin == $emplogin )
	{
		$correctlogin = 'yes';
		$tempid = $empid;
		echo 'correct login found';
	} else
	{
		$correctlogin == 'no';
	}

?>
<?php endwhile; ?>
<?php
	if ( $correctlogin == 'yes' )
	{
		$mysql = new mysqli('localhost','root','','arco') or die('There was a problem connecting to the database');
		$stmt = $mysql->prepare('INSERT INTO timetable VALUES (NULL,NULL,?,?)');
		$stmt->bind_param('ii',$tempid,$eventtype);
		$stmt->execute();
		$stmt->close();
	} elseif ( $correctlogin == 'no' )
	{
		echo 'Wrong employee code';
	}
?>
</div>
</body>

 

CREATE TABLE IF NOT EXISTS `employees` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `empname` varchar(30) NOT NULL,
  `emplogin` int(11) NOT NULL,
  `groupid` int(11) NOT NULL,
  PRIMARY KEY (`empid`)
)

 

CREATE TABLE IF NOT EXISTS `timetable` (
  `timeid` int(11) NOT NULL AUTO_INCREMENT,
  `when` datetime NOT NULL,
  `empid` int(11) NOT NULL,
  `eventtype` int(11) NOT NULL,
  PRIMARY KEY (`timeid`)
)

 

there is one employee currently in the database:

empid = 1 empname= Nouman emplogin= 1991 groupid=1

 

After typing in 1991 into the form and hitting submit, I receive the 'echo 'correct login found';' but when I load my database in phpmyadmin, the timetable has nothing inserted into it. If I type the wrong login in, I am returned with the correct response: echo 'Wrong employee code';. So far that means I am able to retrieve the code input, check to see if it matches an employee in the database, but then it won't allow it to write them into the timetable.

 

now again, more than going forward I am worried about my execution and implementation of php and mysql above. If you feel anything is done strangely, or if there is a neater way, or just anything you'd do different, I appreciate it greatly. And as always, thanks for reading.

[xtopolis]

Hi,

 

First off, the examples I supplied were to get you thinking about the big picture of your application, as well as to show you some better database normalization.  There is no "right way", only the way that works for you and meets your needs.  It should become clear what your needs are, and how things can be broken down after you've drawn it out on a piece of paper.

 

The method I suggested for tracking (Different event types) may or may not be suited to your needs.  I have not created a timeclock type system before, so I was only speculating about how it might be used.

 

 

I've briefly glanced at your code.  It is my suggestion that you would start off on validating the employee id first (since everything relies on being linked to the employee) and then take them to a second page with options of what they can do.  (ie:, if their last event was a SHIFT START, they would have: START LUNCH, END SHIFT available to them, but not END LUNCH or START SHIFT).

 

Regarding it not inserting into the database, this might be because you have 4 arguments in the prepare statement, and 2 in the bind_param statement.  I am not sure since they are null whether or not they need to be represented.  It also appears that your schema for Timetable isn't being match in the statement.  You give it NULL, NULL, int, int, when they fields are int(auto), datetime, int, int.  At the very least it should be datetime(like: date("Y-m-d H:i:s")), int, int

 

Also, if you are not seeing error messages, first try adding:

error_reporting(E_ALL); ini_set('display_errors', 1);

to the top of the page (below <?php of course) and it will show ALL errors.  If you still see no errors, trying copying the query into MySQL directly (or via PHPMyAdmin) to see if it complains.

 

Hope that helps.

[Noumes]

I've began writing this down on paper, mostly just arrows and boxes but it does seem to help. Right off the bat I notice something. I was also planning a page to display employee's logged in, and options for them to select such as checkout, and take a break dependent on what their current status was (as you mentioned above also); however, here is where I hit a brick wall.

 

When thinking out to execute this, I had no idea how to tell if the employee was currently clocked in, on break...and so on.

 

I came out with two methods I assumed would be workable:

 

1. Retrieving the last eventtype in timetable where empid.employees = empid.timetable.

ex: -

 

timeid(int,auto)  |  empid(int) | when(datetime)        |  eventtype(int)

 

  57              |      2      | 2009-07-14 16:01:20  | 1

 

  58              |      2      | 2009-07-14 17:00:00  | 3

END TABLE

 

so if eventype 1 = checked in, and eventtype 3 = On break, a query which would display an employees current eventtpe would show employee #2 currently on break and for that user displaying an option to end break and resume work.

 

Is that possible? is there a such query to work that operation?

 

the second method is going back and fourth.

2. Adding another row to Employees 'empstatus' which would update corresponding to eventtype changes. so if

Bob had empstatus 1, he just logged in and would be shown take a break option or log out option.

 

thanks for the help thus far, its been great.

[xtopolis]

Regarding adding the column of "groupid" to the employee table:

-This is only acceptable if they will only ever have one group, or you represent multiple groups by another manner, such as binary.  I personally am not good at binary, so I prefer to have a lookup table so I can easily manage a user belonging to any number of groups.  I also do it this way so as to plan for growth/changes in the application.  Perhaps your needs show that all employees must belong to the employee group for mailing out announcements... but new hires must belong to the new hire group as well for management reason.

-You don't know that this situation will ever happen, but the more normalized your tables are, the less trouble you would have implementing these new requirements should they come along.

-What you want to stay away from is using a single column to store multiple values like (1,3,22) as a comma-separated-string.. that's bad news right there.

 

Note: You may want to break your name column in the employee table into firstname and lastname columns.  This can aid in selection, presentation, etc...

NOTE2: I made a mistake naming the timetable column "when".  It is a reserved word, and should not be used as a column name.  Sorry.

 

 

Regarding your query for getting the employees with a certain status (on break, etc):

-I will look into this more later, or someone else could comment with a sample one.  I was trying to do a small example but got busy..  I'm not sure whether my proposed design will work or not, but it's worth testing.  Also, there are already made solutions out there that you could review and model your own after.