php – Change of date format to save and retrieve from MySQL

Question:

In a web application under jQuery , I am using jQuery UI 's datepicker .

I apply a regional configuration to the datepicker so that the date is delivered in dd/mm/yyyy .

The user selects a date in the datepicker , which I send by Ajax to php to be recorded in MySQL and then, through another Ajax , I retrieve the date that has been recorded.

For example, the user selects 18/11/2016 in the datepicker , but in MySQL when saving this date, it remains as 000-00-00 ; it's like he didn't understand the date and recorded a 0 .

To solve the above, in the php that receives the date, I did the following:

$fechaz = $_POST["fecha"];
$partes = array();
$partes = explode("/",$fechaz);
$arreglo = array($partes[2], $partes[1], $partes[0]);
$nueva_fecha = implode("-", $arreglo);

$con = conectar();

$sql1 = "INSERT INTO fechas (fecha) VALUES ('$nueva_fecha')";
$q1 = mysqli_query( $con, $sql1

In this way, now in MySQL 2016-11-18 is recorded.

When retrieving this date from MySQL in the following Ajax , and displaying it in another datepicker , I get an incorrect date of 28/04/2022 . I tried to do the same thing in the php that gets the date, using explode() and implode() , but it doesn't work with the retrieved date.

In this regard, the questions are:

Is there a way, through jQuery 's own statements or functions that allow, in the case of recording the date, to transform the format of the datepicker dd/mm/yyyy to the format required by MySQL yyyy-mm-dd , so that in the php doesn't have to use explode() and implode() ?

And related to the same thing, is there any statement or function that allows me to transform the format of the date that comes from MySQL yyyy-mm-dd and take it to dd/mm/yyyy , in order to display it correctly in the datepicker ?

---

I have taken @aldanux's suggestion and recording the date in MySQL has worked for me, but presenting the retrieved date in the datepicker does not work for me:

The invocation to record and retrieve the date is:

var fecha1 = $("#datepicker").val();

$.ajax({
    url: "grabar_fecha.php",
    data: "fecha=" + fecha1,
    type: "POST",
    dataType: "json",
    success: function (respuesta) {
        console.log("GRABADO");
    }
});

$.ajax({
    url: "recupera_fecha.php",
    data: "saludo=" + "buscar",
    type: "POST",
    dataType: "json",
    success: function (res) {
        $("#datepicker").datepicker("setDate", res.fecha);
    }
}); 

The php that records was as:

require_once "funciones/conexiones.php";
$fechaz = $_POST["fecha"];
$f = str_replace('/', '-', $fechaz);
$fx = date('Y-m-d', strtotime($f));

$con = conectar();
$sql1 = "INSERT INTO fechas (fecha) VALUES ('$fx')";
$q1 = mysqli_query( $con, $sql1) or die("Problemas al ejecutar la consulta");

This is correctly recording the date, in my example 2016-11-18

The php that retrieves the date was left as:

require_once "funciones/conexiones.php";

$saludo = $_POST['saludo'];
$con = conectar();

$sql = "SELECT max(id) as id, fecha FROM fechas";
$q = mysqli_query($con, $sql);
$info = array();

while ($datos = mysqli_fetch_array($q)) {
    $ident=$datos["id"];
    $fec=$datos["fecha"];
};

$fx = date('Y-m-d', strtotime($fec));
$info['id'] = $ident;
$info['fecha'] = $fx;
echo json_encode($info);

This isn't working right, as the date it retrieves is 1970-01-01 .

What is missing?

Answer:

You can do this simply with the strtotime() function, in conjunction with PHP's date() .

See Demos

EDIT: I didn't notice the slash / in the date:

To convert from / to - for the database you change it with the str_replace() function above:

function format_date_dd_mm_Y($date)
{
    return date('d/m/Y', strtotime($date));
}

function format_date_Y_mm_dd($date)
{
    return date('Y-m-d', strtotime($date));
}        

$fecha1 = '18/11/2016';
$fecha1 = str_replace('/', '-', $fecha1); // Cambia los '/' a '-'

$fecha2 = '2016-11-18';

echo format_date_Y_mm_dd($fecha1); // 18/11/2016 => 2016-11-18
echo format_date_dd_mm_Y($fecha2); // 2016-11-18 => 18/11/2016