SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dates from form into MYSQL getting 1969-12-31 in MYSQL

    Help I am using these parameters and cannot solve the issue of wrong date in the database:

    $DateAA=date('Y/m/d', $_POST['DateAA']);
    $DateBB=date('Y/m/d', $_POST['DateBB']);

    $query = sprintf ( "insert into places ( DateAA, DateBB )values ( '%s', '%s' ) )", mysql_real_escape_string($DateAA),
    mysql_real_escape_string($DateBB) );

    I have to use the sprintf since this string is actually quit larger with many other string variables. etc... but all I get is the incorrect date of 1969-12-31 and 1969-12-31 respectfully in MYSQL. The date was gotten from a form variable where DATEAA and DATEBB are assigned a date chosen by the user. In this case it was 1/10/2009 and 7/12/2012. Of course the date really doesnt matter I am just showing the format.

    I have tried many things form this forum and every other but can't get passed the error from string to a date in the database.

    I tried this solution posted in this forum "$date = mysql_real_escape_string($_POST['date']);
    $date = implode('-', array_reverse(explode('-', $date))); " and many many others but all that occurs with this one is the the date in the database goes to 0000-00-00.
    Last edited by mikeypp; Oct 7, 2011 at 01:03. Reason: typos

  2. #2
    SitePoint Addict goldensona's Avatar
    Join Date
    Jun 2011
    Posts
    226
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mikeypp View Post
    Help I am using these parameters and cannot solve the issue of wrong date in the database:

    $DateAA=date('Y/m/d', $_POST['DateAA']);
    $DateBB=date('Y/m/d', $_POST['DateBB']);

    $query = sprintf ( "insert into places ( DateAA, DateBB )values ( '%s', '%s' ) )", mysql_real_escape_string($DateAA),
    mysql_real_escape_string($DateBB) );

    I have to use the sprintf since this string is actually quit larger with many other string variables. etc... but all I get is the incorrect date of 1969-12-31 and 1969-12-31 respectfully in MYSQL. The date was gotten from a form variable where DATEAA and DATEBB are assigned a date chosen by the user. In this case it was 1/10/2009 and 7/12/2012. Of course the date really doesnt matter I am just showing the format.

    I have tried many things form this forum and every other but can't get passed the error from string to a date in the database.

    I tried this solution posted in this forum "$date = mysql_real_escape_string($_POST['date']);
    $date = implode('-', array_reverse(explode('-', $date))); " and many many others but all that occurs with this one is the the date in the database goes to 0000-00-00.
    mysqldate format is yyyy-mm-dd. is your post date is in this format?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried storing the dates as timestamps? That always seems to work for me.

  4. #4
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by goldensona View Post
    mysqldate format is yyyy-mm-dd. is your post date is in this format?
    no it is in this format mm/dd/yyyy as follws when i put a loop on the _POST: DateAA: 7/18/2011
    DateBB: 10/14/2012

  5. #5
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by programthis View Post
    Have you tried storing the dates as timestamps? That always seems to work for me.
    I changed the MYSQL to timestamp and had the same result. it became 0000-00-00 00:00:00 and

    0000-00-00 00:00:00

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mikeypp View Post
    I changed the MYSQL to timestamp and had the same result.
    Change your MySQL column types to INTEGER(10) rather than timestamp. You also have to convert your dates to timestamps:

    $DateAA=strtotime($_POST['DateAA']);
    $DateBB=strtotime($_POST['DateBB']);

  7. #7
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by programthis View Post
    Change your MySQL column types to INTEGER(10) rather than timestamp. You also have to convert your dates to timestamps:

    $DateAA=strtotime($_POST['DateAA']);
    $DateBB=strtotime($_POST['DateBB']);
    OK COOL some progress now I get 1309759200 and 1354345200 which I can see is going to need to be converted back when I place it back on the screen. Let me go to the documents and see what I need to do to do that?

  8. #8
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank YOU programthis this is solved I used the below code in my loop after i did what u said above and got exactly what I was wanting!

    <td><?php echo date('m-d-Y',$DateAA); ?></td>
    <td><?php echo date('m-d-Y',$DateBB); ?></td>

    A solution in one hour and 25 minutes. Awesome.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad I could help!

    In case you want to format the date the way it was entered by the user (7/12/2012), use date('n/j/Y', $DateAA);
    n = numeric month without leading 0
    j = day of the month without leading 0

  10. #10
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Dates are stored in the database by entering them in as CCYY-MM-DD - the internal format is different and you can have it formatted however you want when you extract it from the database. The only limitation is on what format it has to be in when you store it.

    Never use any type of field other than date or timestamp to store a date as if you use anything else you lose most of the date processing functionality built into the database.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  11. #11
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Dates are stored in the database by entering them in as CCYY-MM-DD - the internal format is different and you can have it formatted however you want when you extract it from the database. The only limitation is on what format it has to be in when you store it.

    Never use any type of field other than date or timestamp to store a date as if you use anything else you lose most of the date processing functionality built into the database.
    Well the problem set I ran into would not work any other way?

    So what direction can you point me to make sure that I can store it in the DB as a date field since it is has gone from a string variable at the form level then converted to an integer in order to store it at the DB level? Now I would like it as a date variable in the DB so that way I can do against it at the server level. (Which is the very thing I want to do for the application I am writing.) Currently if I store it as a integer 10 I have to add an extra step when I run a database procedure against it to do the math required in the application. (Something I actually am seeing as a problem.)

    Any direction of how I can store a string that is formatted as a date first then needs to be converted into a date so that it can be imported using a sprint with MYSQL would be greatly appreciated. I have tired almost every hybrid in every help I could find.

    Thanks in advance.

  12. #12
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    If it is in any kind of date format to start with then the PHP date functions ought to be able to convert it to CCYY-MM-DD format.

    Have you tried:

    date('Y-m-d', $_POST['DateAA']);
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  13. #13
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    If it is in any kind of date format to start with then the PHP date functions ought to be able to convert it to CCYY-MM-DD format.

    Have you tried:

    date('Y-m-d', $_POST['DateAA']);
    Yes as follows:



    Originally Posted by mikeypp
    Help I am using these parameters and cannot solve the issue of wrong date in the database:

    $DateAA=date('Y/m/d', $_POST['DateAA']);
    $DateBB=date('Y/m/d', $_POST['DateBB']);

    the / or - did not matter i ended with a date field in the database with 12/31/2011 date

  14. #14
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by mikeypp View Post
    Yes as follows:


    $DateAA=date('Y/m/d', $_POST['DateAA']);
    $DateBB=date('Y/m/d', $_POST['DateBB']);

    the / or - did not matter i ended with a date field in the database with 12/31/2011 date
    The / would matter as it is required to be -

    If using - doesn't work that means that $_POST['DateAA'] doesn't contain a valid timestamp that you are converting into a date. Your visitors are not likely to know how to enter a timestamp and are more likely to enter the date in some other format so most likely you need to convert $_POST['DateAA'] into a timestamp BEFORE using it in the date function in order to get the code to work
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  15. #15
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $DateAA=date("Y-m-d", strtotime($_POST['DateAA']));
    $DateBB=date("Y-m-d", strtotime($_POST['DateBB']));

    Solved it using the original DATE variable in MYQL. Thanks everyone who helped me with an intermediate solution which gave me some pice of mind and then pushed me into the right direction!


Tags for this Thread

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
  •