SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict hurricanedan's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question regarding inserting a date value into MySQL

    I have a string value that is a date 'dd-mm-yyyy'. I would like to store it in MySQL as a date but I do not think my MySQL statements syntax is correct and it is failing.

    Code:
    $query=mysql_query ("INSERT INTO 
    $tbl_name (id, hostname, hostaddress, partydate) 
    VALUES (NULL, '$hostname', '$hostaddress', '$partydate' ")
    );
    I thought I might could re-write it as DATE('$partydate') but that does not work either.

    Any help would be nice,

    Dan

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the correct format for inserting is yyyy-mm-dd. if you change your data to that format, then the query above will succeed.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you should also remove the NULL bit for the id column. other RDMBS's that support serially generated primary keys (a.k.a. auto_increment) do not support inserting NULL in that column.

  4. #4
    SitePoint Addict hurricanedan's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is what it was.

    I used the php function explode to reorder the string, is there a more correct way to handle that or is it fine?

    Thanks a lot,
    Dan

  5. #5
    SitePoint Addict hurricanedan's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    you should also remove the NULL bit for the id column. other RDMBS's that support serially generated primary keys (a.k.a. auto_increment) do not support inserting NULL in that column.
    But don't I need something as the place holder? If I remove 'NULL' from the values area would I also remove 'id' from the into area?

    Dan

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hurricanedan View Post
    I used the php function explode to reorder the string, is there a more correct way to handle that or is it fine?
    works ok for me. the other way would be to use substrings.
    Quote Originally Posted by hurricanedan View Post
    If I remove 'NULL' from the values area would I also remove 'id' from the into area?
    yes.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's not the most straightforward way to deal with this, but I have taken a liking to PHP's strtotime() function. If you're allowing freeform date entry into a text box and only asking them to enter it in a specific way, you'll be able to accept a wider variety of inputs this way too.

    PHP Code:
    $date date("Y-m-d"strtotime($partydate)); 

  8. #8
    SitePoint Addict hurricanedan's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    I have taken a liking to PHP's strtotime() function.

    PHP Code:
    $date date("Y-m-d"strtotime($partydate)); 
    Thanks. I will keep that in mind. Do you know if strtotime allows for variations of the date, i.e. 09-26-2007 vs 26-09-2007? or can you tell strtotime what format you are putting in?

    Dan

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thread moved to php forum
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •