Convert jQuery date to php date or mysql date to be inserted correctly in database

Question

I use jQuery datepicker with the following code

$(".item").datepicker({
  showWeek: true,
  firstDay: 1,
  minDate: -30,
  dateFormat: 'mm-dd-yy'
});

The value of that datepicker will be sent via ajax to php function which insert a data in mysql table.

The date column type from mysql database is Datetime.

Every time when read value from datepicker input value, the date column in database is empty shows 00-00-0000 00:00:00.

I am newbie in php and maybe I made somewhere a mistake.

the php piece of code

mysqli_query($connect, "Insert into tab(date) values ('".$myData."')");

how to format javascript date in that right for mysql ?

Answer 1

As in JavaScript date values are created using let date = new Date("2017-01-26");, you have to use the same format when formatting the date in your PHP script.

Just convert yout php date as following format date('Y-m-d', $date).

This will give you the right format for javascript <input type="date"> field.

Answer 2

I use a convertor (source code here: https://github.com/lingtalfi/DatePickerHelper/blob/master/DatePickerHelper.php)

Usage:

<?php
    echo DatePickerHelper::convertFromDatePickerToPhpDate("dd/mm/yy"); // prints d/m/Y
    echo DatePickerHelper::convertFromPhpDateToDatePicker("Y-m-d"); // prints yy-mm-dd
    $birthdayDate = "09/12/1944"; // your input from the web
    echo DatePickerHelper::convertFromNumericInputToMysqlDate($birthdayDate, "d/m/Y"); // 1944-12-09

-

class DatePickerHelper
{


    private static $map = [
        "yy" => "<1>",
        "y" => "<2>",
        "MM" => "<3>",
        "M" => "<4>",
        "mm" => "<5>",
        "m" => "<6>",
        "DD" => "<7>",
        "D" => "<8>",
        "oo" => "<9>",
        "o" => "<10>",
        "dd" => "<11>",
        "d" => "<12>",
    ];

    private static $map2 = [
        "<1>" => 'Y',
        "<2>" => 'y',
        "<3>" => 'F',
        "<4>" => 'M',
        "<5>" => 'm',
        "<6>" => 'n',
        "<7>" => 'l',
        "<8>" => 'D',
        "<9>" => 'z', // note: php doesn't have "day of the year with three digits", but this is the closest
        "<10>" => 'z',
        "<11>" => 'd',
        "<12>" => 'j',
    ];


    private static $mapRegex = [
        'Y' => '<1>',
        'y' => '<2>',
        'm' => '<3>',
        'n' => '<4>',
        'd' => '<5>',
        'j' => '<6>',
    ];

    private static $mapRegex2 = [
        '<1>' => '(?P<year4>[0-9]{4})',
        '<2>' => '(?P<year2>[0-9]{2})',
        '<3>' => '(?P<month_leading>[0-9]{2})',
        '<4>' => '(?P<month_no_leading>[0-9]{1,2})',
        '<5>' => '(?P<day_leading>[0-9]{2})',
        '<6>' => '(?P<day_no_leading>[0-9]{1,2})',
    ];


    public static function convertFromDatePickerToPhpDate(string $datePickerFormat): string
    {
        $map = self::$map;
        $map2 = self::$map2;
        $first = str_replace(array_keys($map), array_values($map), $datePickerFormat);
        return str_replace(array_keys($map2), array_values($map2), $first);
    }

    public static function convertFromPhpDateToDatePicker(string $phpDate): string
    {
        $map2 = array_flip(self::$map2);
        $map = array_flip(self::$map);
        $first = str_replace(array_keys($map2), array_values($map2), $phpDate);
        return str_replace(array_keys($map), array_values($map), $first);
    }


    /**
     * @param string $input , the string to convert, the format of this string should match the given phpFormat
     *                  Plus, it must contain exactly:
     *                          - one day component
     *                          - one month component
     *                          - one year component
     *
     * @param string $phpFormat , all components of the phpFormat  have to be one of those:
     *          - Y: year, four digits
     *          - y: year, two digits
     *          - m: numeric month, with leading zeros
     *          - n: numeric month, without leading zeros
     *          - d: numeric day of the month, with leading zeros
     *          - j: numeric day of the month, without leading zeros
     */
    public static function convertFromNumericInputToMysqlDate(string $input, string $phpFormat)
    {
        $map = self::$mapRegex;
        $map2 = self::$mapRegex2;
        $first = str_replace(array_keys($map), array_values($map), $phpFormat);
        $pattern = str_replace(array_keys($map2), array_values($map2), $first);

        if (preg_match('!' . $pattern . '!', $input, $match)) {
            $day = $match['day_leading'] ?? $match['day_no_leading'] ?? null;
            if (null !== $day) {
                $day = (int)$day;
                $month = $match['month_leading'] ?? $match['month_no_leading'] ?? null;
                if (null !== $month) {
                    if (
                        array_key_exists("year4", $match) ||
                        array_key_exists("year2", $match)
                    ) {
                        // a component of each type is there, we will be able to return a result
                        if (array_key_exists("year4", $match)) {
                            $year = (int)$match['year4'];
                        } else {
                            // assumed it's 20, but we don't know really, that sucks.
                            // That's why you should use year4 instead...
                            $year = "20" . $match['year2'];
                            $year = (int)$year;
                        }

                        return $year . "-" . sprintf('%02s', $month) . "-" . sprintf("%02s", $day);
                    }
                }
            }
        }
        return false;
    }

}

Answer 3

The date format in mysql is YYYY-MM-DD so you could use strtotime:

$myDataSQL = date("Y-m-d", strtotime($myData));
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");

By the way, I would recommend using prepared statements to avoid sql injection problems although it does not really matter much in this specific case. I always use prepared statements so I don't have to think about it.

Edit: It seems strtotime needs / separators for that to work.

If you are on PHP 5.3+ you can use the DateTime class:

$date = DateTime::createFromFormat('m-d-y', $myData);
$myDataSQL = $date->format('Y-m-d');
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");

Answer 4

A different take on jeroen's answer which is just as suitable

$myDataSQL = date("c", strtotime($myData));
mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')");

I find using the ISO 8601 date everywhere easier to handle timezones as they are visible. Plus it inserts into mysql.

Also in the past I have had issues with jQuery datapicker in the needing to specify a better format in its initialisation.

Maybe try

$(".item").datepicker({
    showWeek: true,
    firstDay: 1,
    minDate: -30,
    dateFormat: "yy/mm/dd"
});

Answer 5

queryAccording to mysql documentation:

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

so you could just change the format in which datepicker is taking the date.

$( ".item" ).datepicker({ dateFormat: "yy-mm-dd" });

putting that into your update should do the trick and you wont even need additional server operations for parsing. Just make sure it will reach the server safely.

Answer 6

Can you first verify that the datepicker is posting a correct value to the server?

Try alert the value somewhere.

If you have the correct input from the javascript, the php part of the script can be done like this:

if (isset$_GET['date']){$date=$_GET['date'];}
$date=date("Y-m-d h:i:s",strtotime($date));

Echo out to confirm you have it right, and finally insert that $date into the table.