Jump to content

How to fix conflict in Time Format from uploading data in .xml to mysql database?

- - - - -
Started by newphpcoder, Oct 18 2011 11:58 PM

  • Please log in to reply
5 replies to this topic

#1
newphpcoder
Posted 18 October 2011 - 11:58 PM

newphpcoder

    Programming Professional

  • Members
  • PipPipPipPipPipPip
  • 479 posts
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
Orjan
Posted 19 October 2011 - 01:11 PM

Orjan

    Writes binary right handed and hex left handed

  • Moderators
  • 3,299 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#
AM/PM is not used in 24h format, so that is correct. Verify that the stored times in the database is correct according to the xml file as first. then, verify your SELECT by building it piece by piece so you can follow and see where the error occurs
__________________________________________
I study Information Systems at Karlstad University when I'm not on CodeCall

#3
newphpcoder
Posted 19 October 2011 - 05:41 PM

newphpcoder

    Programming Professional

  • Members
  • PipPipPipPipPipPip
  • 479 posts
Some programmers told me to combine the date and time into one column..NOw I will tried to combined it..


Thank you

---------- Post added at 01:41 AM ---------- Previous post was at 12:04 AM ----------

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

#4
newphpcoder
Posted 19 October 2011 - 09:20 PM

newphpcoder

    Programming Professional

  • Members
  • PipPipPipPipPipPip
  • 479 posts
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

#5
Orjan
Posted 20 October 2011 - 08:00 AM

Orjan

    Writes binary right handed and hex left handed

  • Moderators
  • 3,299 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#
well, 6:00 + 7:30 is 13:30, so it seems like it calculates as you requested. are you having IN and OUT times in the same table? then you need to calculate the time between each in and out instead. To be able to help you, please show how the XML and how the database table is set up, and we can give you better advice!
__________________________________________
I study Information Systems at Karlstad University when I'm not on CodeCall

#6
newphpcoder
Posted 20 October 2011 - 04:57 PM

newphpcoder

    Programming Professional

  • Members
  • PipPipPipPipPipPip
  • 479 posts
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.
Back to PHP Development


1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users



  1. CODECALL Programming Forum
  2. → Web Development Forum
  3. → PHP Development
IPB skins by Skinbox