Menu
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
[code=php]
<?php
$n=08;
var_dump($n); /* returns int(0) */
?>
[/code]
[code=php]
<?php
$n='08';
var_dump($n); /* returns string(2) "08" */
?>
[/code]
[code=php]
$date = substr($date,0,-13);
$time = substr($time,11,-4);
[/code]
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.[/QUOTE]
[code=php]
SELECT SECT_TO_TIME(SUM(TIME_TO_SEC('time')))AS TOTAL FROM Employee
[/code]
[code=php]SELECT SUM(TIMESTAMPDIFF(SECOND,START_OF_TIMESTAMP
, END_OF_TIMESTAMP
)) FROM table
[/code]
[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]
[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]
<i>
</i>SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time))) AS total FROM employee WHERE EMP_NO = '100603' ORDER BY 'Date';
0.1.9 — BETA 6.16