SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do i convert data type to "datetime"?

    Hi,
    A "form" within an "HTML" page collects data from users. The data I refer to is called "starttime" and "enddtime" and it should come in
    a format such as "2013-07-23 10:00:00".
    In order to process the HTML data I created the following PHP file:
    Code:
    <?php
    $con=mysqli_connect("localhost","root","root","db");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    
    $sql="INSERT INTO June2013 (remark, startdate, enddate,interval_time,institution, company)
    VALUES
    (
     '$_GET[Remark]',
     '$_GET[startDate]',
     '$_GET[endDate]',
     TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),
     '$_GET[Institution]',
     '$_GET[company]'
     )";
    
    if (!mysqli_query($con,$sql))
      {
      die('Error: ' . mysqli_error($con));
      }
    echo "1 record added";
    ?>
    For values: startDate="2013-07-23 10:00:00", endDate="2013-07-23 16:00:00" I got the value: 9999.99.
    Why didn't I get 6?!
    Mayby the value from the HTML page came in a format different then "DATETIME"?
    So, how do I convert data which comes from an HTML file (through a FORM) to datetime?
    Thanks

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,388
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Hi deotpit,

    What data type is your interval_time column? The TIMEDIFF function is going to return a TIME value, so the column needs to be of the same type.
    Although it doesn't relate to your question, you really want to be checking/escaping your $_GET values before you insert them into the DB.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi fretburner,
    Good to see you again :-) .
    Intreval time column in my mysql table is "DECIMAL(6,2).
    The $_GET escaping value is for me to see that the form sends the value I want.
    Anyway: Why with:
    Code:
    $sql="INSERT INTO June2013 (remark, startdate, enddate,interval_time,institution, company)
    VALUES
    (
     '$_GET[Remark]',
     '$_GET[startDate]',
     '$_GET[endDate]',
     TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),
     '$_GET[Institution]',
     '$_GET[company]'
     )";
    I get
    Code:
    TIMEDIFF('$_GET[endDate]','$_GET[startDate]'),
    9999.99 when enddate was inserted as: "2013-07-23 16:00:00" and startdate: "2013-07-23 10:00:00"? if that is its date
    value, how do i convert it into "DATETIME"?. I think my problem is convering values from HTML file inyo datetime and integers. Is there a way to do so with PHP?

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,388
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by deotpit View Post
    Intreval time column in my mysql table is "DECIMAL(6,2).
    That's the problem, the column needs to be of the TIME type to store the output from the TIMEDIFF function.

  5. #5
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot fretburner. I'll make the change and see if it works. Both cases I'll let you know (assuming you're interested :-) )

  6. #6
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,388
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by deotpit View Post
    Thanks a lot fretburner. I'll make the change and see if it works. Both cases I'll let you know (assuming you're interested :-) )
    Absolutely, it's always nice to hear when things work out, and not just when they don't.

  7. #7
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Absolutely, it's always nice to hear when things work out, and not just when they don't.
    Hi fretburner,
    I'm late with my response because I was busy trying more and more solutions to the above problem.
    It turns out that there's only one solution suitable for me and that is the solution you suggested.
    There maybe more solutions (I fount lots of then in Stack overflow) but it didn't suit the wide range of my problem.
    So i'll have to cordially thank you again and maybe that will be an explanation why I don't use google search's solution.
    In sitepoin I get a more "personal" advice. Google search's suggestions are good in general but not if one's background is different then the general, so to speak.
    Thank you and hope I can enjoy more of your assistance in the future. I'm afraid I'll need it...


Bookmarks

Posting Permissions

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