SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 Code:
    <?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 == $emp $cell->nodeValue;
                      if ( 
    $index == $employee $cell->nodeValue;
                      if ( 
    $index == $last $cell->nodeValue;
                      if ( 
    $index == $mi $cell->nodeValue;
                      if ( 
    $index == $date $cell->nodeValue;
                      if ( 
    $index == $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:

    PHP Code:
    SELECT SEC_TO_TIMESUMTIME_TO_SEC'time' ) ) ) AS TOTAL_TIME
    FROM employee
    WHERE EMP_NO 
    '100063' 
    and the output is 00:00:00

    Thank you so much...

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    $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());
        }

    =>
    PHP Code:
    $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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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 == $emp $cell->nodeValue;
                      if ( 
    $index == $employee $cell->nodeValue;
                      if ( 
    $index == $last $cell->nodeValue;
                      if ( 
    $index == $mi $cell->nodeValue;
                      if ( 
    $index == $date $cell->nodeValue;
                      if ( 
    $index == $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:
    Code:
    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. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,833
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,833
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    The difference between two datetimes is obtained by subtracting one from the other. You then would just need to convert the result into hours.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give me an example for that...Thank you

  10. #10
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •