...

View Full Version : Saving XML file to Mysql Database



newphpcoder
10-18-2011, 11:21 AM
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

Old Pedant
10-19-2011, 12:27 AM
No, the times in your XML are in this format:
1899-12-31T08:00:00.000

you need to STRIP OFF the "1899-12-31T".

So just use a bit of PHP code to do that when encountering a time.

Actually, your dates also have the time in them. Example:

2011-10-10T00:00:00.000

For them, you really should strip off the "T00:00:00.000"

newphpcoder
10-19-2011, 02:01 AM
No, the times in your XML are in this format:
1899-12-31T08:00:00.000

you need to STRIP OFF the "1899-12-31T".

So just use a bit of PHP code to do that when encountering a time.

Actually, your dates also have the time in them. Example:

2011-10-10T00:00:00.000

For them, you really should strip off the "T00:00:00.000"

Thank you...

I will try to find the code for strip off. I want to correct my previous post.

the time has an AM/PM and the date is mm/dd/yyyy

Thank you

newphpcoder
10-19-2011, 02:25 AM
I can't find syntax for strip off of the "1899-12-31T" and "T00:00:00.000":confused:

newphpcoder
10-19-2011, 02:58 AM
I edit my code to remove "1899-12-31T08 and .000" in time and date "T00:00:00.000" using this code:



$date = substr($date,0,-13);
$time = substr($time,11,-4);


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

Old Pedant
10-19-2011, 05:12 AM
You are wrong. The XML file that you put in your ".zip" file does *NOT* use AM or PM. And it shouldn't, because it's using the XML standard format which uses a 24-hour clock, the same as does MySQL. T08:00:40 *is* 8:00:40 AM. If it were PM it would be T20:00:40

When you are ready to *DISPLAY* the time data, *THEN* you could ask either MySQL or PHP to display it using AM and PM. But you SHOULD leave it in 24-hour clock format both in XML and in the database.

newphpcoder
10-19-2011, 06:59 AM
here is my new .xml file with AM and PM

Old Pedant
10-19-2011, 10:37 PM
Yes? So what's the problem?

This cell, for example:
<Cell><Data ss:Type="DateTime">1899-12-31T18:30:15.000</Data></Cell>
is perfect. If you strip off the date that time value will store in MySQL just fine.

And, as I said, when it comes time to *DISPLAY* the value, either PHP or MySQL can convert it into 6:30:15 PM for you.

I don't use PHP, but the way to do it in MySQL is easy:


SELECT DATE_FORMAT(yourFieldName,'%h:%i:%s %p') as theTime
FROM yourTable

newphpcoder
10-20-2011, 02:01 AM
I will try to combine the date and time into one column and also one field in database...


Thank you...

But how can I sum the time?

newphpcoder
10-20-2011, 03:42 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...

Old Pedant
10-20-2011, 11:41 PM
The datatype in the table should be either TIME or DATETIME.

Any you can't add TIME (or DATE or DATETIME) values directly.

Probably the best way to do this is convert the time to a number of minutes (or seconds...or even milliseconds) and add *those* up and then convert them back to a time value.

But what "time of employee" values are you trying to add? Are you trying to get the number of hours worked in a week or something like that?

newphpcoder
10-21-2011, 03:13 AM
The datatype in the table should be either TIME or DATETIME.

Any you can't add TIME (or DATE or DATETIME) values directly.

Probably the best way to do this is convert the time to a number of minutes (or seconds...or even milliseconds) and add *those* up and then convert them back to a time value.

But what "time of employee" values are you trying to add? Are you trying to get the number of hours worked in a week or something like that?

Yes, I want to get the number of hours per employee...

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.

Old Pedant
10-21-2011, 05:02 AM
You don't have anything in your data or your table that indicates whether a give record is the startWorkTime or the endWorkTime.

Suppose you have an employee who starts work at 10PM and ends works at 6AM. You wouldn't have two records on the same day at least some days.

Pardon me, but this just seems like a really poor overall database design. And that may be the fault of the XML feed, in which case the XML feed needs fixing.

Old Pedant
10-21-2011, 05:07 AM
A better database design might be


EmployeeNo Lastname Firstname Middlename Date TimeIn TimeOut
100603 Test Test Test 2011-10-01 7:00:00 AM 7:00:00 PM
100604 JC JC JC 2011-10-01 8:00:00 AM 6:30:00 PM
100603 Test Test Test 2011-10-02 8:00:00 AM 6:40:00 PM

Old Pedant
10-21-2011, 05:08 AM
And, actually, an even better design would *NOT* have the Lastname, Firstname, Middlename fields in this table. Those should all be in an EMPLOYEES table and the EmployeeNo is sufficient in this table.

newphpcoder
10-21-2011, 05:08 AM
You don't have anything in your data or your table that indicates whether a give record is the startWorkTime or the endWorkTime.

Suppose you have an employee who starts work at 10PM and ends works at 6AM. You wouldn't have two records on the same day at least some days.

Pardon me, but this just seems like a really poor overall database design. And that may be the fault of the XML feed, in which case the XML feed needs fixing.

If I used to columns combining datetime like for example indatetime and outdatetime it should be ok???

Thank you

Old Pedant
10-21-2011, 05:19 AM
Sure.

And that's easy.



$date = substr($date,0,-13); // the code from your post #5
$time = substr($time,11,-4); // ditto
$datetime = $date . " " . $time;

newphpcoder
10-21-2011, 07:06 AM
Yes, I will remove the lastname. firstname, and middlename, uhmmm if I have separate time and time out...how can I get the total hours of employee..If the hours of employee per day is only 8 hours...no matter he time in early or he logout lately. Like for example the employee schedule is 8:00AM - 5PM
He time in 7:30 AM and he time out 6:00 PM...how can I compute his hours.
Thank you

newphpcoder
10-21-2011, 07:10 AM
Sure.

And that's easy.



$date = substr($date,0,-13); // the code from your post #5
$time = substr($time,11,-4); // ditto
$datetime = $date . " " . $time;


If I have only one column datetime for timein and timeout is it ok???How can I compute the hours of employee...


Thank you...

newphpcoder
10-21-2011, 07:15 AM
Sure.

And that's easy.



$date = substr($date,0,-13); // the code from your post #5
$time = substr($time,11,-4); // ditto
$datetime = $date . " " . $time;


You mean, In my .xml file the date and time is separately? then in saving to my database it will be combine???

Thank you

Old Pedant
10-21-2011, 09:37 PM
Yes. I *assumed* that you don't have any control over the format of the XML file (which is terrible, by the way). I assumed you have to just live with it and so you would do the conversion(s) as you processed the XML.

If you have control of the XML, then redesign it now and save a bunch of grief and work.

newphpcoder
10-24-2011, 02:19 AM
What if the date and time is in one column and I separate the datetime in and datetime out? is it much better???I want a simply and easy saving and calculation of the attendance per employee...Thank you...

Old Pedant
10-24-2011, 07:59 AM
You can do it either way.

The version you had originally would work, with one *BIG* ASSUMPTION!!!

You have to assume that nobody starts working on one day and ends working the next day.

That is, nobody starts at say, 9PM and then gets off work at 5AM the next day.

*IF* you can make that assumption, it's easy, based on this INSERT:


$sql = "INSERT INTO employee (name, last, mi, date, time ) "
. " VALUES ('$employee', '$last', '$mi', '$date', '$time')";

You could do:


SELECT E1.name, E1.last, E1.mi,
sec_to_time(SUM(unix_timestamp(E2.time) - unix_timestamp(E1.time))) AS totaltime
FROM employee AS E1, employee AS E2
WHERE E1.date = E2.date
AND E1.time < E2.time
GROUP BY E1.name, E1.last
ORDER BY E1.name, E1.last, E1.mi

newphpcoder
10-24-2011, 08:05 AM
Now I revise my code and my database.

I only have empno, timein and timeout ...the timein and timeout is datetime. and now I encountered problem in computation of hours.


I tried this data to upload:
100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.
100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout thereís no exemption because he late on his work. So it should has deduction or minus in his total hours.

and it saves it database:
100603 2011-10-01 05:35:00 2011-10-01 13:35:00
100603 2011-10-02 05:25:00 2011-10-01 13:55:00
100603 2011-10-02 05:40:00 2011-10-01 13:40:00


I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I donít have idea how can be possible it is.

and I tried this code for computing the hours per day:


select sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;


and the result of this code is:

totalhours:
08:00:00
08:30:00
08:00:00



and the result is

the first is correct because the real schedule is 5:35 AM - 1:35 PM
the second is wrong it should be 8 hours only even he timein early and timeout late.
the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.

I hope someone can help me. Thank you

newphpcoder
10-24-2011, 08:07 AM
You can do it either way.

The version you had originally would work, with one *BIG* ASSUMPTION!!!

You have to assume that nobody starts working on one day and ends working the next day.

That is, nobody starts at say, 9PM and then gets off work at 5AM the next day.

*IF* you can make that assumption, it's easy, based on this INSERT:


$sql = "INSERT INTO employee (name, last, mi, date, time ) "
. " VALUES ('$employee', '$last', '$mi', '$date', '$time')";

You could do:


SELECT E1.name, E1.last, E1.mi,
sec_to_time(SUM(unix_timestamp(E2.time) - unix_timestamp(E1.time))) AS totaltime
FROM employee AS E1, employee AS E2
WHERE E1.date = E2.date
AND E1.time < E2.time
GROUP BY E1.name, E1.last
ORDER BY E1.name, E1.last, E1.mi


Thank you...I really appreciate..



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum