[SOLVED] Timeclock Database Help

[techiefreak05]

I was give the task of creating a simple employee Timeclock script to make for a client of mine.

I have a database setup with a table called "employees" that hold all the employees information, and I have a "timePunch" table to store the time punch information of each employee. I am confused on the best way to store the time punch information.

 

What is the best way to store, employee id, and current date/time into the database and be later retrieved from the database.

[Barand]

Store the timepunch as a DATETIME column. Store NOW() into it when the employee call the time punch function in your script.

 

[pre]

employee        timepunch                  activity

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

id    ---+      id                    +--- id

name      |      timepunch (datetime)  |    act_name

etc      +---<  empID                  |

                activityID      >-----+

[techiefreak05]

Alright, but how do I set the "activityID". the act_name is either "in" or "out" ... but Im not sure how to come up with the activity IDs

[Barand]

I threw in Activity as an optional extra. I didn't know if you just wanted to record in/out or whether you wanted a time booking system to record time spent on various client or project activities throughout each day.

 

If all you need is IN/OUT then forget the activity table and the activityID in the timepunch table would simply be IN or OUT.

[techiefreak05]

Ah, ok. thanks!

[techiefreak05]

Wait how would I be able to only punch in if you're not already, and to punch out only if you're punched ni...

 

i would have to check fi a riw exists based on date()... but I am not certain on the arguments to use on date()

[Barand]

If there are no records today for the user then it's an IN.

 

If the last was IN then next should be OUT and vice versa. But you may have to allow for situations where the user forgot, or was unable, to clock in or out.

 

For example, what if an employee goes direct to a client's office at 9am and arrives back at base at 12 noon. He now wants to clock out for lunch, but even though he started at 9am he hasn't clocked in yet? If you do it automatically because there is no clocking today then you clock him in 3 hours late.

[techiefreak05]

I get it.

But is there any obviou reason why this code doesnt work? I dont get any errors at all, but it doesnt insert any rows.

 

<?php
ob_start();
session_start();

include 'db.php';

if($_POST["doPunch"]){
   if(!$_POST['emp_id'] || !$_POST['emp_pw']){
      header("location: index.php?er=1");
      exit;
   }
   $_POST['emp_id'] = trim($_POST['emp_id']);

   $md5pass = md5($_POST['emp_pw']);
   $result = confirmUser($_POST['emp_id'], $md5pass);

   if($result == 1){
      header("location: index.php?er=2");
      exit;
   }
   else if($result == 2){
      header("location: index.php?er=3");
      exit;
   }


$checkLastPunch=mysql("SELECT * from timePunch WHERE empID = '$_POST[emp_id]' ORDER BY ID DESC LIMIT 1") or die(mysql_error());
while($r=mysql_fetch_assoc($checkLastPunc)){

if($r["activityID"] == "In"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'Out'
);") or die(mysql_error());
}else if($r["activityID"] == "Out"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}else{
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());

}

}
}

?>

[Barand]

If it doesn't find any rows the while() loop won't execute.

 

As only a single row returned you don't need a loop anyway.

[techiefreak05]

I did some modifications, but it's still not doing anything.

 

<?php
ob_start();
session_start();

include 'db.php';

if($_POST["doPunch"]){
   if(!$_POST['emp_id'] || !$_POST['emp_pw']){
      header("location: index.php?er=1");
      exit;
   }
   $_POST['emp_id'] = trim($_POST['emp_id']);

   $md5pass = md5($_POST['emp_pw']);
   $result = confirmUser($_POST['emp_id'], $md5pass);

   if($result == 1){
      header("location: index.php?er=2");
      exit;
   }
   else if($result == 2){
      header("location: index.php?er=3");
      exit;
   }


$cc=mysql("SELECT * from timePunch WHERE empID = '$_POST[emp_id]' ORDER BY `ID` DESC") or die(mysql_error());

if(mysql_num_rows($cc)<1){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES ('$_POST[emp_id]', 'Out');") or die(mysql_error());
}else{

$r=mysql_fetch_assoc($cc);

if($r["activityID"] == "In"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'Out'
);") or die(mysql_error());
}else if($r["activityID"] == "Out"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}else{
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}


}
}

?>

 

[xander85]

Are you using phpmyadmin or have access to the mysql DB? I would try executing your queries on the database directly using an arbitrary Employee ID to make sure your query is working.

 

I would change your code to do the following:

 

Select only the columns you need (activityID) and timepunch where employee id = $_POST['empid] AND timepunch = date(curdate())

 

then check amount of mysql_num_rows():

if 0, then not punched in today. Punch them in

if 1+, check to make sure time is before current time and if they are punched in/out, then punch them in again (like returning from lunch) or punch out

 

You could also add certain restrictions for for each employee. For example, if someone tries to punch in/out before/after certain times perform certain actions. This is just a suggestion and you would obviously have to consult your client.

 

Sorry for providing any code. I would suggest a few other things:

Change activityID to a tinyint column and use 0/1 for In/Out (To save space and for optimization if the db REALLY grows)

and make sure you use indexes properly since this table could become large VERY fast depending on how many employees the company has.

 

Hope this helps.

[xander85]

I did some modifications, but it's still not doing anything.

 

<?php
ob_start();
session_start();

include 'db.php';

if($_POST["doPunch"]){
   if(!$_POST['emp_id'] || !$_POST['emp_pw']){
      header("location: index.php?er=1");
      exit;
   }
   $_POST['emp_id'] = trim($_POST['emp_id']);

   $md5pass = md5($_POST['emp_pw']);
   $result = confirmUser($_POST['emp_id'], $md5pass);

   if($result == 1){
      header("location: index.php?er=2");
      exit;
   }
   else if($result == 2){
      header("location: index.php?er=3");
      exit;
   }


$cc=mysql("SELECT * from timePunch WHERE empID = '$_POST[emp_id]' ORDER BY `ID` DESC") or die(mysql_error());

if(mysql_num_rows($cc)<1){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES ('$_POST[emp_id]', 'Out');") or die(mysql_error());
}else{

$r=mysql_fetch_assoc($cc);

if($r["activityID"] == "In"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'Out'
);") or die(mysql_error());
}else if($r["activityID"] == "Out"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}else{
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}


}
}

?>

 

 

Change your query and use a where condition that checks the current date. I have a feeling your query is simply not returning any results. Try executing it in phpmyadmin or from the mysql console.

 

Also, make sure you are echoing out as much as you can. Echo your query statement and try adding:

 

<? echo "<pre>";

print_r($_POST);

echo "</pre>";

?>

 

At the bottom of your page to print out your POST array to make sure it is doing what you want/expect.

[techiefreak05]

This should work... i tried the first query in phpmyadmin, and it returned 0. because the table is empty.. ok no problems there.. but in my code i have if rows=0, then do this query...but it doesnt work. let me see what i can come up with.

 

<?php
$cc=mysql("SELECT * from timePunch WHERE empID = '$_POST[emp_id]' ORDER BY `ID` DESC") or die

(mysql_error());

if(mysql_num_rows($cc)<1){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES ('$_POST[emp_id]', 'Out');") or die(mysql_error());
}else{

$r=mysql_fetch_assoc($cc);

if($r["activityID"] == "In"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'Out'
);") or die(mysql_error());
}else if($r["activityID"] == "Out"){
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}else{
mysql_query("INSERT INTO `timePunch` (`empID`,  `activityID`)
VALUES (
'$_POST[emp_id]', 'In'
);") or die(mysql_error());
}
?>

[xander85]

Change the first line, I don't believe you are actually executing the query (but I could be wrong):

 

Change it to mysql_query()

 

$cc=mysql_query("SELECT * from timePunch WHERE empID = '$_POST[emp_id]' ORDER BY `ID` DESC") or die (mysql_error());

 

I would add some test data into your table to help in troubleshooting. The rest of your code looks sound, just put some data in to test.

 

Also, I would suggest adding a data condition to your where clause. If your table balloons to thousands of rows you are putting added strain on the database with your rather vague query.

 

If you have never played around with the EXPLAIN syntax, search google for a tutorial and try to make a habit out of using this to make your queries as efficient as possible.

[techiefreak05]

I feel really stupid. I didnt even NOTICE it was just "mysql" and not "mysql_query" .. haha never made that mistake before.

 

testing now...

 

It works!

Thanks All!

[Barand]

Most of us read what we we expect it to be instead of what's there.