Skip navigation
Currently Being Moderated

PHP/mySQL code help please

Oct 23, 2011 8:55 AM

Hi all,

 

Thanks to David Powers fab tutorial I am now well on my way to competeing my first dynamique website using PHP/mySQL, (thanks David ), however I am stuck on one particular area....can anyone please help?

 

I know that the mySQL database uses the format YYYY/MM/DD for dates, and that is fine. I've sorted out how to display in a more regular format (eg. 1st September 2011) for my application, but I am stuck on how to convert a more regular formatted date entered by the site user into the mySQL format.

 

Ideally I would like for the users entry or say either 1/9/11 or 1-9-11 (or something along those lines, to be recognised and displayed on the entry form, but to have a backend function that converts the data to mySQL format. I seem to be reading plenty of forum posts which say this can be done, but unfortunately I can't quite seem to fathom how, or where the code should sit. Would someone mind taking a look at my code page to actually identify what I need to insert, and equally as importantly...where!!!

 

Along these lines...is it possible to have a mini-calendar date picker that could be embedded next to the date entry field to ensure that a valid date is entered and in the correct format? Where could this be sourced?

 

Thanks in advance of some much needed help

 

Mark

 
Replies
  • Currently Being Moderated
    Oct 23, 2011 9:24 AM   in reply to noiseboy1970

    Check the date picker widget that is available via DW's Widget browser....

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 23, 2011 10:42 AM   in reply to noiseboy1970

    This general method is terriffic at converting various representations of dates to UNIX timestamps which can then be used with the PHP date() command -

     

    echo date("Y-m-d", strtotime('1/9/11'))

     

    You feed strtotime() a string representation of a date, and then use date() with the desired format to convert it back to a more standard representation of that date.  Try it....

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 24, 2011 12:17 PM   in reply to noiseboy1970

    Actually I believe MySQL expects dates to be YYYY-MM-DD format.  And you would use the code I suggested just before you are inserting the data into the MySQL table.  You could embed it in the query string, for example -

     

    $query = "INSERT INTO table (field1, field2, ...) VALUES ('value1'," . $date("Y-m-d", strtotime($your_date)) . "', value3, ...)"

     

    Does that help?

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 24, 2011 2:33 PM   in reply to noiseboy1970

    Change this -

     

    GetSQLValueString($_POST['date'], "date"),

     

    to this -

     

    GetSQLValueString(date('Y-m-d'),strtotime($_POST['date']), "date"),

     

    See if that works....

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 24, 2011 3:57 PM   in reply to noiseboy1970

    No, that has not worked

     

    What happens when you try it?  How does it not work?

     

    What is the value you placed in $_POST['date']?

     

    Show me the modification to the code that you have made, please.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 3:50 AM   in reply to noiseboy1970

    GetSQLValueString(date('Y-m-d'),strtotime($_POST['date']), "date"),

     

    Make that line look like this -

     

    GetSQLValueString(date('Y-m-d',strtotime($_POST['date'])), "date"),

     

    This was my mistake - sorry.  The MySQL DATE datatype should be in the format of YYYY-MM-DD.  If that's the datatype of this 'date' field, then this code should work.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 11:55 AM   in reply to noiseboy1970

    date("m/d/Y", strtotime($row_rsWhatever['date'])

     

    assuming "m/d/Y" is the format you want to use.

     

    Take advantage of http://www.php.net/manual/en for the arcana of this and other PHP commands.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 1:03 PM   in reply to noiseboy1970

    So, I don't know what your question is.  I thought my previous suggestion worked for you.  What is it you want to do now, and where do you want to do it?

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 1:21 PM   in reply to noiseboy1970

    Does that make sense?

     

    Not exactly.  How do I know what the format *is* when the data is first entered into the database?  How are you controlling that format?

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 1:55 PM   in reply to noiseboy1970

    So show me the code for how you populate the update form field, not for how you are updating the database.  That's where the conversion would be done.  NOW if you also want to change the format of the data in the database when this record is updated, then you would make exactly the same change I recommended previously.

     
    |
    Mark as:
  • Currently Being Moderated
    Oct 25, 2011 2:38 PM   in reply to noiseboy1970

    <input name="date" type="text" id="date" value="<?php echo date("m-d-Y", strtotime($row_updatePost['date'])); ?>" />

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 22, 2012 8:18 AM   in reply to MurraySummers

    Murray, you are my hero too!

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points