Conflict in Time Format from uploading data .xml to mysql database

PHP
1

Good day!

I created a payroll system and now I have a problem in Time Field from .xml the time is 08:00:00 AM when I used my php code for uploading the .xml file it display : 1899-12-31T08:00:00.000, to remove the : 1899-12-31T and .000

this is my file in .xml
time: 8:00:00 AM
6:00:00 PM

and it save in database as
time : 08:00:00
18:00:00

i used the code substr

I used this code for importing .xml file:


<?php
$data = array();


$con = mysql_connect("localhost", "root","");
if (!$con) { 
  die(mysql_error());
}
$db = mysql_select_db("db_upload", $con);
if (!$db) { 
  die(mysql_error());
}

$sql = "select * from employee";
$result =  mysql_query($sql, $con);
if (!$result) {
    die(mysql_error());
}
$total = mysql_num_rows($result);
if ($total > 0) {
    $sql = "delete from employee";
    $result =  mysql_query($sql, $con);
    if (!$result) {
        die(mysql_error());
    }
}
  
function add_employee($emp, $employee, $last, $mi, $date, $time)
  {
      global $data; 
      
      $con = mysql_connect("localhost", "root","");
      if (!$con){ die(mysql_error());}
      $db = mysql_select_db("db_upload", $con);
      if (!$db) { 
          die(mysql_error());
      }

    
      $emp = $emp;
      $employee = $employee;
      $last = $last;
      $mi = $mi;
      $date = substr($date,0,-13);
      $time = substr($time,11,-4);
      $date = $date;
      $time = $time;
           
      $sql = "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
      mysql_query($sql, $con);
      
      
      $data []= array('EMP_NO' => $emp, 'Name' => $employee, 'last' => $last, 'mi' => $mi, 'date' => $date, 'time' => $time);
      
  }
  
  if ( $_FILES['file']['tmp_name'] )
  {
      $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
               
      $rows = $dom->getElementsByTagName( 'Row' );
      global $last_row;
      $last_row = false;
      $first_row = true;
      foreach ($rows as $row)
      {
          if ( !$first_row )
          {
              
              $emp = "";
              $employee = "";
              $last = "";
              $mi = "";
              $date = "";
              $time = "";
              
              
              $index = 1;
              $cells = $row->getElementsByTagName( 'Cell' );
          
              foreach( $cells as $cell )
              { 
                  $ind = $cell->getAttribute( 'Index' );
                  if ( $ind != null ) $index = $ind;
                  
                  if ( $index == 1 ) $emp = $cell->nodeValue;
                  if ( $index == 2 ) $employee = $cell->nodeValue;
                  if ( $index == 3 ) $last = $cell->nodeValue;
                  if ( $index == 4 ) $mi = $cell->nodeValue;
                  if ( $index == 5 ) $date = $cell->nodeValue;
                  if ( $index == 6 ) $time = $cell->nodeValue;
                  $index += 1;
              }
         
              if ($emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                    $last_row = true;
              }      
              else {
                  
                    add_employee($emp, $employee, $last, $mi, $date, $time);
              }      
          }
          if ($last_row==true) {
              $first_row = true;
          }     
          else {
              $first_row = false;
          }
        
      }
  }
  ?>
  
  <html>
  <body>
  <table>
  <tr>
      <th>Employee Attendance</th>
  </tr>

  <?php foreach( $data as $row ) { ?>
  <tr>
  <td><?php echo( $row['EMP_NO'] ); ?></td> 
  <td><?php echo( $row['Name'] ); ?></td>
  <td><?php echo( $row['last'] ); ?></td>
  <td><?php echo( $row['mi'] ); ?></td>
  <td><?php echo( $row['date'] ); ?></td>
  <td><?php echo( $row['time'] ); ?></td>
  </tr>
  <?php } ?>
  </table>
  </body>
 </html>

and the data save in time field is : 08:00:00 the AM was not display and it is 24 hours.

and now I tried to SUM the time field in database using this code:


SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( 'time' ) ) ) AS TOTAL_TIME
FROM employee
WHERE EMP_NO = '100063'

and the output is 00:00:00

Thank you so much…

2 


$sql = "select * from employee";
$result =  mysql_query($sql, $con);
if (!$result) {
    die(mysql_error());
}
$total = mysql_num_rows($result);
if ($total > 0) {
    $sql = "delete from employee";
    $result =  mysql_query($sql, $con);
    if (!$result) {
        die(mysql_error());
    }
}

=>


$sql = "TRUNCATE TABLE employee";
if (!mysql_query($sql, $con)) {
    die(mysql_error());
}

Second of all, SUM’ing them without a GROUP BY clause will cause the query to sum all of the employees’ times together.

I dont understand the purpose of ‘time’ field. Is it recording how many hours the employee worked? Why not store this as an INT (or FLOAT, if you do parts-of-hours) rather than a TIME.

3

If I used int or float, is there possible to compute the hours per employee?What is the best data type for time field?

Thank you

4

I tried INT and FLOAT datatype in time column, and the data save in database is only number 18…
But in my xml file it is 6:00:00 PM

I really don’t know what data type shopuld I used and also the format cells in .xmkl file…

I also tried to combine date and time i one field…
And I dont know how can I add the time of employee…

Thank you for your help…

5

I have date and time column separately…in time column the data is in and out of the employee…now I need the total sum of per employee working hours
I tried this code for the importing the .xml fiole and save to database:


<?php
$data = array();

$con = mysql_connect("localhost", "root","");
if (!$con) { 
  die(mysql_error());
}
$db = mysql_select_db("db_upload", $con);
if (!$db) { 
  die(mysql_error());
}

$sql = "select * from employee";
$result =  mysql_query($sql, $con);
if (!$result) {
    die(mysql_error());
}
$total = mysql_num_rows($result);
if ($total > 0) {
    $sql = "delete from employee";
    $result =  mysql_query($sql, $con);
    if (!$result) {
        die(mysql_error());
    }
}
  
function add_employee($emp, $employee, $last, $mi, $date, $time)
  {
      global $data; 
      
      $con = mysql_connect("localhost", "root","");
      if (!$con){ die(mysql_error());}
      $db = mysql_select_db("db_upload", $con);
      if (!$db) { 
          die(mysql_error());
      }

      $emp = $emp;
      $employee = $employee;
      $last = $last;
      $mi = $mi;
      $date = substr($date,0,-13);
      $time = substr($time,11,-4);
     $date = strtotime($date); 
     $date = date('d-m-Y', $date);
      $time = strftime('%I:%M %p', strtotime($time));


           
      $sql = "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
      mysql_query($sql, $con);
      $data []= array('EMP_NO' => $emp, 'Name' => $employee, 'last' => $last, 'mi' => $mi, 'date' => $date, 'time' => $time);
      
  }
  
  if ( $_FILES['file']['tmp_name'] )
  {
      $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );       
      $rows = $dom->getElementsByTagName( 'Row' );
      global $last_row;
      $last_row = false;
      $first_row = true;
      foreach ($rows as $row)
      {
          if ( !$first_row )
          {

              $emp = "";
              $employee = "";
              $last = "";
              $mi = "";
              $date = "";
              $time = "";
              
              
              $index = 1;
              $cells = $row->getElementsByTagName( 'Cell' );
          
              foreach( $cells as $cell )
              { 
                  $ind = $cell->getAttribute( 'Index' );
                  if ( $ind != null ) $index = $ind;
                  
                  if ( $index == 1 ) $emp = $cell->nodeValue;
                  if ( $index == 2 ) $employee = $cell->nodeValue;
                  if ( $index == 3 ) $last = $cell->nodeValue;
                  if ( $index == 4 ) $mi = $cell->nodeValue;
                  if ( $index == 5 ) $date = $cell->nodeValue;
                  if ( $index == 6 ) $time = $cell->nodeValue;
                  $index += 1;
              }
         
              if ($emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                    $last_row = true;
              }      
              else {
                  
                    add_employee($emp, $employee, $last, $mi, $date, $time);
              }      
          }
          if ($last_row==true) {
              $first_row = true;
          }     
          else {
              $first_row = false;
          }
       
      }
  }
  ?>
  
  <html>
  <body>
  <table>
  <tr>
      <th>Employee Attendance</th>
  </tr>

  <?php foreach( $data as $row ) { ?>
  <tr>
  <td><?php echo( $row['EMP_NO'] ); ?></td> 
  <td><?php echo( $row['Name'] ); ?></td>
  <td><?php echo( $row['last'] ); ?></td>
  <td><?php echo( $row['mi'] ); ?></td>
  <td><?php echo( $row['date'] ); ?></td>
  <td><?php echo( $row['time'] ); ?></td>
  </tr>
  <?php } ?>
  </table>
  </body>
 </html>

and i have data

date: 2011-10-01
time: 07:30:00
date: 2011-10-01
time: 06:00:00

and i tried also this code to sum the hours:


SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time))) AS total FROM employee WHERE EMP_NO = '100603' ORDER BY 'Date';

and output is : 13.30

but I want the result is the total hours that the employee work

6

If you need both the date and time then it is far more efficient, easier to maintain, and less error prone to store them in a single datetime firld. A date field is effectively a datetime field set to midnight and a time field is effectively a datetime field set to 31st December 1969 so having the two separate when you actually need both is pointless.

7

Can you help me…in my problem in date and time…Honestly i dont know how to do that…Someone give me ideas but I cant understand… If I used one column with datetime how can I get the total hours per employee if the column datetime consist of datetime in and out of employee…
Thank you

Thank you…

8

The difference between two datetimes is obtained by subtracting one from the other. You then would just need to convert the result into hours.

9

Can you give me an example for that…Thank you

10

here is the .xml file data sample
Employee No Lastname Firstname Middlename Date Time
100603 Test Test Test 1/10/2011 7:00:00 AM
100603 Test Test Test 1/10/2011 7:00:00 PM
100604 JC JC JC 1/10/2011 8:00:00 AM
100604 JC JC JC 1/10/2011 6:30:00 PM
100603 Test Test Test 2/10/2011 8:00:00 AM
100603 Test Test Test 2/10/2011 6:40:00 PM
100604 JC JC JC 2/10/2011 7:30:00 AM
100604 JC JC JC 2/10/2011 6:00:00 PM

and here is the sql:

Employee No Lastname Firstname Middlename Date Time
100603 Test Test Test 2011-10-01 7:00:00
100603 Test Test Test 2011-10-01 7:00:00
100604 JC JC JC 2011-10-01 8:00:00
100604 JC JC JC 2011-10-01 6:30:00
100603 Test Test Test 2011-10-02 8:00:00
100603 Test Test Test 2011-10-02 6:40:00
100604 JC JC JC 2011-10-02 7:30:00
100604 JC JC JC 2011-10-02 6:00:00

Thank you…

The hours of employee is computed 8 hours a day… even the employee enter in office early or late to go out.

11

So you assume an employee works 8 hours a day, no matter what time they start/end that day? (Dangerous - what if an employee works 4 hours?)

And you’re storing the person coming and going - you should really make that one entry (a start time and end time)

If you make that assumption:
SELECT EMP_NO, COUNT(EMP_NO) FROM Employee GROUP BY EMP_NO,Date; = number of days employees worked. You can add *8 after the count command to get hours (again, assuming they worked 8 hours each day)

If you DONT make that assumption, you’ll have to do some math in PHP to figure it out, if you dont restructure your table.

12

Thank you…I will clarify it first to my client…
What if I separate the start and end date?It should easy to calculate the hours of employee?and the datatype is datetime?

Thank you