Saving XML file to Mysql Database

@newphpcoderOct 18.2011

I upload xml file but I encountered error in time format. the xml file time 08:00:00 did not save in mysql the data in sql time is 00:00:00

I will attach my code

Thank you

to post a comment

PHP

13 Comments(s) _↴

@KorOct 18.2011 — #The numbers which start with 0 followed by any other digit(s) are interpreted as octal numbers by default, so that 08 becomes 0 when translated literally as a decimal integer

[code=php]
 <?php
 $n=08;
 var_dump($n); /* returns int(0)  */
 ?>
 [/code]

See also:

http://php.net/manual/en/language.types.integer.php

Check your code and make sure your data is taken rather as strings, not as integers.

[code=php]
 <?php
 $n='08';
 var_dump($n); /* returns string(2) "08"  */
 ?>
 [/code]

@newphpcoderauthorOct 18.2011 — #The data from .xml file has a format cells : custom hh:mm:ss, I think it is integer because it saves as 00:00:00 rather than 08:00:00

@newphpcoderauthorOct 18.2011 — #I want to correct my previous post, I have data in .xml time : 1:49:45 PM , my problem is how can I insert it in my mysql database with the same data.

Thank you...

@KorOct 18.2011 — #OK:

http://www.tizag.com/mysqlTutorial/mysql-date.php

http://www.ntchosting.com/mysql/insert-date.html

And make sure that the variable $date is previously formatted as a MySQL valid format, like :YYYY-MM-DD HH:mm:SS

@newphpcoderauthorOct 18.2011 — #Good day!

in my .xml file the date column: mm/dd/yyyy how can I insert it to database with the same format, and the time column is hh:mm:ss AM/PM 11:40:00 AM and 8:02:00 PM

I really don't know how can I resolve it..

Thank you...

@newphpcoderauthorOct 19.2011 — #I edit my code to remove "1899-12-31T08 and .000" in time and date "T00:00:00.000" using this code:

[code=php]
 $date = substr($date,0,-13);
 $time = substr($time,11,-4);
 [/code]

before that code the output is:

date:2011-10-01T00:00:00.000

time: 1899-12-31T08:00:40.000

and the .xml file:

date: 10/1/2011

time: 8:00:40 AM

now it save in database but the time AM and PM did not display and save.

The time and date field is very important for me to resolve because of computing the attendance.

Thank you

@KorOct 19.2011 — #
and the .xml file:

date: 10/1/2011

time: 8:00:40 AM

now it save in database but the time AM and PM did not display and save.

The time and date field is very important for me to resolve because of computing the attendance.

Thank you[/QUOTE]
As far as I know, you can not insert AM or PM into MySQL tables. I suppose you should previously transform data into a 24h hours format.

@newphpcoderauthorOct 19.2011 — #As far as I know, you can not insert AM or PM into MySQL tables. I suppose you should previously transform data into a 24h hours format.[/QUOTE]

Now it save as 24 hours my problem now is when I sum the time the output is 00:00:00

I sum the 08:00:00 and 18:00:00

using this code:

[code=php]
 SELECT SECT_TO_TIME(SUM(TIME_TO_SEC('time')))AS TOTAL FROM Employee
 [/code]

Thank you

@KorOct 19.2011 — #Why to sum two [I]24h time notations[/I]?

I believe that you don't make the distinction between a date (time) and duration. A day can not have more then 24 hours, thus the notation hh:mm:mm can not cover more than 24 hours, if it is used [I]within a date display[/I].

If you want to add them as being a date, perform the sum [I]before[/I] sending it to the DB, and use the full date: YYYY,MM,DD hh:mm:ss

The MySQL date syntax is made to store a [I]date (time)[/I], not a [I]duration[/I]. If you want to store a duration, just store it as any other[I] string[/I] as [COLOR="Blue"]"[/COLOR]hh:mm:ss[COLOR="Blue"]"[/COLOR]. But don't expect MySQL to give it a special meaning regarding the date or time, nor to be able to perform a math operation with it.

@newphpcoderauthorOct 19.2011 — #Is it easy or possible that in .xml the date and time is in one column and also in database?But How can I sum the hours per employee?

Thank you

@ericatekkaOct 20.2011 — #You can SUM time together if you drag it back to seconds as time cannot sum of 844 Hrs I believe.

So something like the below perhaps.
[code=php]SELECT SUM(TIMESTAMPDIFF(SECOND,START_OF_TIMESTAMP, END_OF_TIMESTAMP)) FROM table[/code]

Then perhaps run a script like the below on the output.

[code=php]function minutesToHours($minutes)
 {
 $hours = (int)($minutes / 60);
 $minutes -= $hours * 60;
 $minutes = (strlen($minutes) > 1)?$minutes:"0".$minutes;
 $hours = (strlen($hours) > 1)?$hours:"0".$hours;
 $time = $hours.":".$minutes.":00";
 return $time;
 } [/code]

The function above is used for minutes only as I did not need to calc the seconds.

I'm sure there is a function in SQL that would do this for you anyway.

@newphpcoderauthorOct 20.2011 — #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:

[code=php]
 <?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>
 
 
 [/code]

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:

<i>
 </i>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

@newphpcoderauthorOct 21.2011 — #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.

Also in #PHP _↴

Radio buttons, php and updating mysql web-based PHP checklist Text box parameters in HTML form not seen in PHP

Success!

Help @newphpcoder spread the word by sharing this article on Twitter...

Tweet This

Saving XML file to Mysql Database

13 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Saving XML file to Mysql Database

13 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴

Also in #PHP _↴