SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Simple but frustrating datetime php mysql prob

    Hi I'm a newbie having trouble entering a date into my mysql database

    When I write:

    $query = "INSERT INTO test3 (play_date) VALUES ('2007-10-10 10:10:10')";

    it works perfectly...

    however when I set
    $query = "INSERT INTO test3 (play_date) VALUES ('$play_date')";

    and
    $play_date = ('2007-10-10 10:10:10');
    or
    $play_date = date('2007-10-10 10:10:10');
    (or it seems anything else I do)

    it doesn't work.....and instead enters 0000-00-00 00:00:00

    when I say echo $play_date it looks like the perfect format on screen...

    I've been working on this for days...and I'm pulling my hair out...the solution has to be SO simple but I just can't get it right...can someone help me out?

    Helpful suggestions would be greatly appreciated.
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    try this:
    PHP Code:
    $play_date "2007-10-10 10:10:10"
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because probably, you are doing

    Code PHP:
    $play_date = ('2007-10-10 10:10:10');
    and it's becoming

    Code PHP:
    $query = "INSERT INTO test3 (play_date) VALUES ('('2007-10-10 10:10:10')')";

    alternatively, you could also use mysql's NOW() function to insert current datetime ...

    Code PHP:
    $query = "INSERT INTO test3 SET play_date = NOW()";

    EDIT arkinstall told the same thing, before me.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kigoobe View Post
    alternatively, you could also use mysql's NOW() function to insert current datetime ...


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937, is there any difference between these two coz i saw the output is same?
    PHP Code:
    $sql "select CURRENT_TIMESTAMP";
    $sql "select now()"
    I mean any execution time... or something differ?
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    they are the same, but CURRENT_TIMESTAMP is standard SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    they are the same, but CURRENT_TIMESTAMP is standard SQL
    Thank you r937, usually i use CURRENT_TIMESTAMP because it works in PgSQL too. That's why i was curious to work and i did not try to look for the performance differences if there.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  8. #8
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Still not working...despite solutions given

    Hi...

    I just tried:

    $query = "INSERT INTO test2 (play_date) VALUES ('$play_date')";

    where

    $play_date = "2007-10-10 10:10:10";

    and it still didn't work...it entered 0000-00-00 00:00:00 again

    but
    $query = "INSERT INTO test2 (play_date) VALUES ('2007-10-10 10:10:10')";

    worked fine...


    Still frustrated...eventually I'd like to be entering...$Thissunday into mysql

    $date1=strtotime("now");
    $Thissunday = strtotime("Sunday", $date1);

    but I can't even enter a simple variable...any other thoughts?
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  9. #9
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about
    PHP Code:
    $query "INSERT INTO test3 SET play_date = NOW()"
    ? Are you sure you don't wan't to try mysql functions?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    mysql function for current timestamp is good, but he needs more

    he also wants to do this:[quote]eventually I'd like to be entering...$Thissunday into mysql[/code]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    lol - he just sent me a PM with this post 8's question, and I gave him the solution.

    PHP Code:
    date("Y-m-d H:i:s"strtotime("Sunday")); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  12. #12
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    are you really 15 arkinstall ? then you are doing great at such a young age ... till now I thought that Brandon must be the youngest here ...

  13. #13
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    yup. Who's Brandon?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #14
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    another young and talented guy from SP. userID = devbanana

  15. #15
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Tried your solutions and more...but still no luck

    Tried sending

    $query = "INSERT INTO test2(play_date) VALUES ('$play_date')";
    where
    $date1=strtotime("now");
    $play_date= date("Y-m-d H:i:s", strtotime("Sunday", $date1));

    didn't work, just enters 0000-00-00 00:00:00 into database

    so echo'd $query to get

    INSERT INTO test2(play_date) VALUES ('$play_date') showing up on the screen,

    Tried to parse variable by changing $query to:

    $query = "INSERT INTO test2(play_date) VALUES ('".$play_date."')";

    and echo'd $query to get

    INSERT INTO test2(play_date) VALUES ('$play_date')

    then changed $query to

    $query = "INSERT INTO test2(play_date) VALUES (".$play_date.")";

    and finally got the mysql error

    An error occurred on line 34:
    Query: INSERT INTO test2(play_date) VALUES ($play_date)
    MySQL Error: Unknown column '$play_date' in 'field list'


    $query = "INSERT INTO test2(play_date) VALUES ('2008-10-10 10:10:10')";

    works perfectly though...something is wrong with submitting my variable but I can't get it right...

    Help! I have no hair left!!
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  16. #16
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    what if you try:
    Code php:
    $query = sprintf("INSERT INTO test2(play_date) VALUES ('%s')", $play_date);
    Last edited by Jake Arkinstall; Oct 11, 2007 at 13:35. Reason: code fix
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  17. #17
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now I get a simple parse error

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING on line 32
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  18. #18
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by newb40 View Post

    and echo'd $query to get

    INSERT INTO test2(play_date) VALUES ('$play_date')
    :
    This would imply that you are using single quotes, not double.

    PHP Code:
    $query "INSERT INTO test2(play_date) VALUES (".$play_date.")"
    Should be

    PHP Code:
    $query "INSERT INTO test2 (play_date) VALUES ('$play_date')"
    exactly as is.

  19. #19
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    newb, did you try the updated version? I left in the double-quotes by mistake before, so I updated it.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  20. #20
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I use:

    $query = "INSERT INTO test2 (play_date) VALUES ('".$play_date."')";

    then when I echo $query I get:

    INSERT INTO test2 (play_date) VALUES ('$play_date')

    and 0000-00-00 00:00:00 goes into the database
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  21. #21
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    but what about:
    PHP Code:
    $query sprintf("INSERT INTO test2(play_date) VALUES ('%s')"$play_date); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  22. #22
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried

    $query = sprintf("INSERT INTO test2(play_date) VALUES ('%s')", $play_date);

    echo $query still gives

    INSERT INTO test2(play_date) VALUES ('$play_date')

    and 0000-00-00 00:00:00 into database
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  23. #23
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow...shouldn't be this difficult...

    feel like quitting
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux

  24. #24
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What PHP setup are you using?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  25. #25
    SitePoint Enthusiast newb40's Avatar
    Join Date
    Jul 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using PHP version 5.1.6

    through HostMonster...
    Apache version 1.3.37 (Unix)
    PHP version 5.1.6
    MySQL version 5.0.27-standard-log
    Operating system Linux


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
  •