Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Saving XML file to Mysql Database

    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
    Attached Files Attached Files

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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"
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (10-19-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I can't find syntax for strip off of the "1899-12-31T" and "T00:00:00.000":confused:

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I edit my code to remove "1899-12-31T08 and .000" in time and date "T00:00:00.000" using this code:

    PHP 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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    here is my new .xml file with AM and PM
    Attached Files Attached Files

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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:
    Code:
    SELECT DATE_FORMAT(yourFieldName,'%h:%i:%s %p') as theTime
    FROM yourTable
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (10-20-2011)

  • #9
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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?

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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...

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (10-21-2011)

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    A better database design might be
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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