2

I have been trying to follow the example shown from this link below:

PHP mysql insert date format

This is my code sample below:

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    /** Variables */
    $pdate = isset($_POST['pdate']) ? $_POST['pdate'] : '';
    $org   = mysqli_escape_string($dbcon, trim($_POST['org']));
    $city  = mysqli_escape_string($dbcon, trim($_POST['city']));
    $state = isset($_POST['state']) ? $_POST['state'] : '';
    $rio   = mysqli_escape_string($dbcon, trim($_POST['rio']));

    /** Query */
    $q = "INSERT INTO `survey` (id, pdate, org, city, state, rio,   date_created)
        VALUES (NULL, STR_TO_DATE('$pdate', '%M %d, %Y'), '$org', '$city', '$state', '$rio', NOW())";
?>

/** Changing Datepicker Value **/
jQuery(document).ready(function($) {
    /** Datepicker for the Form */
    $('.selector').datepicker('option', 'dateFormat', 'yy-mm-dd');
});

$pdate = "2015-09-28"; Displays like that according to the <?php echo format ?>

There are two queries that you are using, should I use the STR_TO_TIME() or FROM UNIXTIME()

When I try and follow the 3rd step:

$dt = DateTime::createFromFormat('m/d/Y', $_POST['pdate']);
$pdate = $dt->format('Y-m-d');

After submitting the form, I get undefined variable index.

What is it that I am now missing?

Community
  • 1
  • 1
James Gacuca
  • 59
  • 7
  • And what format is your $pdate? – Naruto Sep 21 '15 at 15:09
  • what exactly you want?? If you are looking for date format conversion look at this link,http://www.w3schools.com/php/func_date_date.asp. the link has all possible format for date(). hope that helps you. – Niranjan N Raju Sep 21 '15 at 15:11
  • When you use the `DateTime` class where / for what / how? – David Ferenczy Rogožan Sep 21 '15 at 15:32
  • 1
    **WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). **NEVER** put `$_POST` data directly into a query. Calling the escaping functions manually is asking for serious mistakes, as you have here with `$_POST['state']` being sent through raw. Please pay attention to the multiple warnings on that page about escaping. – tadman Sep 21 '15 at 15:47
  • @Naruto, I have reviewed, kindly look at my examples again. – James Gacuca Sep 21 '15 at 15:53
  • @DawidFerenczy, I have reviewed, kindly look at my examples again. – James Gacuca Sep 21 '15 at 15:53
  • @tadman, I have reviewed, kindly look at my examples again. – James Gacuca Sep 21 '15 at 15:53
  • Wait, in jQuery Datepicker, you have configured the format to `yy-mm-dd`, but in PHP, you're parsing it as `m/d/Y`. Why? Also "*I get undefined variable index*" says nothing useful, just copy&paste the whole error message you're getting, please. – David Ferenczy Rogožan Sep 21 '15 at 15:58
  • @JamesGacuca You haven't changed your escaping at all. This is still scary dangerous code. – tadman Sep 21 '15 at 16:00
  • @Niranjan Please do not link to w3schools. That site promotes dangerously bad habits, the sort demonstrated here with ineffective escaping. Whenever possible link to the *official* PHP documentation. – tadman Sep 21 '15 at 16:01
  • @DawidFerenczy, I am following the example from http://stackoverflow.com/questions/12120433/php-mysql-insert-date-format, but, my problem is from step 3. How do I set it up from there? Also no error is being displayed, only 0000-00-00 is being added to the database. – James Gacuca Sep 21 '15 at 16:26
  • @tadman, do you have a good resource where I can follow how to do it properly, the way I did it, I followed examples from a book. – James Gacuca Sep 21 '15 at 16:28
  • @JamesGacuca Don't follow anything without thinking. How can you write something you don't understand? How it can make a sense to you, to send one format from Datepicker and parse completely different format in PHP? From accepted answer on link you sent, you should use option 1 **or** 2 **or** 3 **or** 4, but **not** together. **These are not steps but options!** (as written there) You should read more carefully. – David Ferenczy Rogožan Sep 21 '15 at 16:31
  • @JamesGacuca I linked to the documentation on using prepared statements in `mysqli` which is all part of the PHP core documentation. Whatever book you're using is wickedly out of date. – tadman Sep 21 '15 at 16:45
  • @DawidFerenczy, is it possible for you to pick one of the options and show how it is done, becuase, I am still not getting the value to show, the closest I got was the datetime as 1969-12-31. – James Gacuca Sep 23 '15 at 16:04
  • @JamesGacuca What's wrong with that date? That's the exact format that MySQL accepts. Or do you need date and time? – David Ferenczy Rogožan Sep 23 '15 at 16:08
  • @JamesGacuca And I'm asking you again: you wrote "*I get undefined variable index*" in your question. But important information is **what index**. Add whole error message to your question, please. How can you expect any help, if you don't provide such information? – David Ferenczy Rogožan Sep 23 '15 at 16:13
  • @JamesGacuca Could mark the answer as an accepted answer, please? It will help others to find a help more easily. – David Ferenczy Rogožan Oct 04 '15 at 21:01

2 Answers2

0

You didn't post the most important information - actual error message - so I can only guess, what's wrong. These are my guesses:

  1. If undefined index is pdate, than you don't have such form control. That could be the raeson, why you have "0000-00-00" as a date in your DB

  2. You have configured Datepicker to output date in a format yy-mm-dd, but you're parsing it as %M %d, %Y in MySQL function STR_TO_DATE or m/d/Y in PHP DateTime's method createFromFormat. That doesn't make a sense.

Since yyyy-mm-dd is MySQL's native format to express a date, you don't need any conversion at all. Just save into a DB what you get from Datepicker.

So I would start with this:

  1. Check what you're getting from your form in PHP, e.g. print what's in $_POST array: var_dump($_POST)
  2. Check if there is a key pdate and contains date in format yyyy-mm-dd
  3. Save it to DB. You're done.
David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
  • For No 1, I am not getting undefined error because I am using isset. For No 2, I My Query I am not using STR_TO_DATE, just INSERT INTO ....... VALUES (NULL, '$pdate', NOW()); For Step 3, when I do Var_Dump, the date is displayed as 2015-09-22. That is where I am getting stuck at. – James Gacuca Sep 23 '15 at 23:47
  • OK, so why don't you like that date format? As I wrote in my answer, that's exactly the format MySQL uses to store dates (`yyyy-mm-dd`), so you don't need any conversion at all. – David Ferenczy Rogožan Sep 24 '15 at 00:35
  • Actually I was able to get it to work, I stripped everything down, and changed the dateformat for jquery, and It saved the right way. – James Gacuca Sep 25 '15 at 01:56
  • Great, I'm glad you solved it. Please mark the answer as an accepted answer, so others can find it easily. – David Ferenczy Rogožan Sep 25 '15 at 02:06
0

The easiest way to Insert Date Format is to do the following using jQuery Date Format See - http://api.jqueryui.com/datepicker/#option-dateFormat

`jQuery`

$( ".selector" ).datepicker({
    dateFormat: "yy-mm-dd"
});
James Gacuca
  • 59
  • 7