PHP date conversion from user input

Question

I am trying to convert a user inputted date so I can use it to search in MySQL. This is my code -

<form name="date_form" action="" method="POST"">
<input type="text" name="start_date" value="<?php echo date('d/m/Y');?>"/>
<input type="submit" name="submit_start" value="Submit" />
<?php 
if(isset($_POST["submit_start"]))
{
$date_1 = mysqli_real_escape_string($dbc, trim($_POST['start_date']));//checking that I am getting something from the input
$newDate = date("Y-m-d", strtotime($_POST['start_date']));//converting date from the input to SQL format
echo '<br>date 1 = '.$date_1.'<br>';
echo 'date 2 = '.$newDate.'<br>';
$start_date = '2013-12-13';
echo 'date 3 = '.$start_date.'<br>';//Just to compare formats

$report = create_user_report($dbc, $start_date);
}

and this is the output

date 1 = 14/12/2013

date 2 = 1970-01-01

date 3 = 2013-12-13

2013-12-13

I was expecting date 2 to be 2013-12-13, the format appears to be ok but the value isnt. I have played with many different ways of getting the value, all have been wrong! So I have two questions please 1. How can I get the correct value in the code above? 2. I want to use this value to search a MySQL table and return a count of dates that match it. Once the above is working, is that the best way to do it - or is there a better way? Many thanks

For your second question please provide more information (by editing the question). How does your db schema look like? Are the cells in a proper Date type? — kero, Dec 14 '13 at 13:41
http://stackoverflow.com/questions/2487921/convert-date-format-yyyy-mm-dd-dd-mm-yyyy this may help you. — Adil Abbasi, Dec 14 '13 at 13:43

score 0 · Answer 1 · answered Dec 14 '13 at 13:37

From the strtotime manual:

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between
the various components: if the separator is a slash (/), then the American m/d/y is 
assumed;     whereas if the separator is a dash (-) or a dot (.), then the European d-m-y
format is assumed.

So:

$newDate = date("Y-m-d", strtotime($_POST['start_date']))

is asking for the 12th day of the 14th month.

Try replacing the / with -

$date = str_replace ( '/' , '-' , $_POST['start_date'])

Thank you all for your help. I didnt realise that the / or - would make a difference. I will add a javascript calendar to prevent user inputs being of different formats. A great help. — user3006586, Dec 14 '13 at 14:19

score 0 · Answer 2 · answered Dec 14 '13 at 13:38

The problem is caused because when confronted with /, strtotime assumes the time to be in the American format of m/d/Y (instead of d/m/Y). Read the manual on strtotime (especially the Notes) for more information.

And because 14/12/2013 is not valid in the American format, you'll get the default time (aka UNIX timestamp 0).

Since this is user input and you cannot be sure if he really means to use the American format or is misusing it, you could do a check before the conversion like this

//check if input is a date in the American format
if (preg_match("#^(\d+)/(\d+)/(\d+)$#", $_POST['start_date'], $matches)) {
    if ($matches[1] > $matches[2]) {
        $day = $matches[1];
        $month = $matches[2];
    } else {
        $day = $matches[2];
        $month = $matches[1];
    }
    $start_date = $month.'/'.$day.'/'.$matches[3];
}

However if a user inputs e.g. 04/05/2013 this will be interpreted in the American format, although the user could have meant it in d/m/Y.

score 0 · Answer 3 · answered Dec 14 '13 at 13:39

0

"Explode" seems to be commonly used in situations like this.

$mydate = $_POST["submit_start"];
list ($y, $m, $d) = explode('/', $mydate);
$mydate = sprintf("%02d-%02d-%04d", $m, $d, $y);

strtotime requires the English date format as input - HERE.

answered Dec 14 '13 at 13:39

TimSPQR

2,964
3
20
29

Kevin Cittadini · Answer 4 · 2013-12-14T13:57:25.050

strtotime() PHP Manual

Take a look over there, it reports

The function expects to be given a string containing an English date format

And that's why your function doesn't work as you expect. In fact, d/m/Y is NOT an american date format. Here, take a look, I made you some examples to let you see how to make it work: Click here - eval.in

<?php 
  echo strtotime(date('m/d/Y'));
  echo strtotime(date('d/m/Y'));
  echo strtotime(date('d-m-Y'));
  echo strtotime(date('d/m/Y'));
  echo strtotime(date('Y-m-d'));
  echo strtotime(date('Y/m/d'));
?>

Produces

Since you'll never know what kind of date format (or if it's actually a date at all) an user may input, I suggest you to use a date picker plugin on your input, that'll be very useful, or you may want to use a regular expression that other user suggested.

For the mysql part you can easly compare two dates with the MySQL Date Function

Since I don't know your query I'll just provide you the part you need for the comparsion in the query:

... WHERE DATE(some_date) = DATE(some_other_date) ...

Where some_date and some_other_date are two valid date formats as written above.

PHP date conversion from user input

4 Answers4