Member Avatar for jrotunda85

For my new code that I'm developing, I have a text box where a user will type in a date as follows:

MM/DD/YYYY (e.g. 05/19/2011)

This date then needs to be converted and inserted into a date/time field on a mySQL database. I've been trying to play around with this a bit though and have been having trouble getting it to register on the database. Any idea on how best to format this field and insert it into the DB?

Also, one option I had thought about is maybe using a javascript dropdown calendar or something, any good recommendations?

Thanks!

  • 6 Contributors
  • 6 Replies
  • 1K Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by divyakrishnan

Dani AI

Generated

Quick, practical summary that ties the replies together and gives a safe, future-proof workflow.

Use a consistent ISO date on input and in the database: store pure dates in a MySQL DATE (displayed/stored as YYYY-MM-DD) and use DATETIME only when you need a time component; TIMESTAMP behaves differently with timezones. (dev.mysql.com)

Preferred flow: have the client supply a normalized date (native input type="date" or a calendar widget), always validate and re-parse on the server, then insert with a parameterized query (prepared statement) to avoid injection. Client pickers like jQuery UI Datepicker make it easy to force an ISO output if you need broad browser support. (api.jqueryui.com)

If you must accept free-text MM/DD/YYYY, parse on the server rather than trusting the raw string. PHPs DateTime parsing routines are the safest programmatic option (note: some DateTime helpers require PHP 5.3+), and an alternative is to let MySQL convert a known format at insert time with STR_TO_DATE if you prefer handling it inside SQL. Both approaches are valid — pick the one that matches your PHP/MySQL versions and your validation needs. (php.net)

Notes tied to the thread: and pointed toward the right tools; correctly flagged the PHP version issue that caused the OPs fatal error; and suggested useful pickers. Final checklist: standardize format at the UI, re-validate and canonicalize on the server, choose the correct MySQL column type, and always insert via prepared statements. (php.net)

Recommended Answers

All 6 Replies

Member Avatar for almostbob

datetime.createfromformat
should cover the neccesary formatting, produces a datetime that you can dump straight into the db

<?php // part of the php script sanitizing the data for input to db where the form date field is inputdate
$DBdate = date_create_from_format('n/d/Y|', $inputdate); // trailing | sets time to 0 
?>

the sql to update the database is pretty ordinary, ignore inputdate and Insert into table $name $details $DBdate etc

Edited by almostbob because: n/a
Member Avatar for urtrivedi

one option is to use jquery-ui datepicker. google for jquery-ui,

IF you want to insert in any other format in mysql then, write your insert query as follows. You may use str_to_date format

$query="insert into tablename value(col1,datecol) 
values ('{$_POST['col1']}',str_to_date('{$_POST['datecol']}','%m/%d/%Y')) ";
Edited by urtrivedi because: n/a
Member Avatar for karthik_ppts

Better use the calendar script. I have attached the calendar files here and the code here. its very simple and easy to use. No need of any conversion

<head>
    <script type="text/javascript" src="DatePicker/calendar.js"></script>
    <script type="text/javascript" src="DatePicker/calendar-setup.js"></script>
    <script type="text/javascript" src="DatePicker/calendar-en.js"></script>
    <link rel="stylesheet" href="DatePicker/calendar-win2k-2.css" />
</head>

<body>
   <input name="fromdate" type="text" class="text_box" id="fromdate" value="<?php echo date('Y-m-d');?>" size="18" readonly="readonly" />
   <img src="images/calendar.png" border="0"  id="date_picker_from" title="Date selector" style="cursor:pointer;" >
                                        <script type="text/javascript">
                                            Calendar.setup({
                                                inputField     :    "fromdate",     // id of the input field
                                                ifFormat       :    "%Y-%m-%d",      // format of the input field
                                                button         :    "date_picker_from",  // trigger for the calendar (button ID)
                                                align          :    "Tl",           // alignment (defaults to "Bl")
                                                singleClick    :    true
                                            });
                                        </script> 
</body>
calendar.png 0.84 KB
DatePicker.zip (79.7 KB)
Edited by karthik_ppts because: n/a
Member Avatar for jrotunda85

datetime.createfromformat
should cover the neccesary formatting, produces a datetime that you can dump straight into the db

<?php // part of the php script sanitizing the data for input to db where the form date field is inputdate
$DBdate = date_create_from_format('n/d/Y|', $inputdate); // trailing | sets time to 0 
?>

the sql to update the database is pretty ordinary, ignore inputdate and Insert into table $name $details $DBdate etc

Thank you all for your suggestions. Bob, I'm trying to use this function and I keep getting the following error --

Fatal error: Call to undefined function date_create_from_format() in xxxxxx on line 13
Member Avatar for Member #120589

re: date_create_from_format

You need at least php 5.3. Check your version.

Member Avatar for divyakrishnan

Visit Following Links

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.