Insert a date value with PHP in MySQL

Question

Insert a date value with PHP in MySQL

Navigation

#1 by (0 votes)

2

I would like to introduce MySQL information through a PHP form with your date of birth. The problem is that no matter how hard I try it in the DB, the 0000-00-00 value is introduced. If I write the date directly on the Insert there is no problem, the problem appears when I try to pass the variables that contain that information.

if(isset($_GET["name"])){

    $name=$_GET['name'];
    $lastName=$_GET['lastName'];
    $age=$_GET['age'];
    $dd=$_GET['dd'];
    $mm=$_GET['mm'];
    $yyyy=$_GET['yyyy'];



    $conexion=mysqli_connect($hostname,$username,$password,$database);

    //INSERT INTO Patient(Patient_Name,Patient_LastName,Patient_Age,Patient_DOB) VALUES ("Pablo", "Mendosa",23,"2000-12-1");


    $consulta= " Insert into Patient(Patient_Name,Patient_LastName,Patient_Age,Patient_DOB) values ('{$name}','{$lastName}','{$age}','{$yyyy}'-'{$mm}'-'{$dd}')";

    $resultado=mysqli_query($conexion,$consulta);

mysql php

asked by Fulano 08.12.2018 в 08:42

source

1 answer

Click RecyclerView - Item URL mapped does not allow ajax to find the resource

score 0 · Accepted Answer

The error occurs because here:

'{$yyyy}'-'{$mm}'-'{$dd}'

Each quote prevents the values of the year, month, day from being treated together.

The solution would be to write the date surrounded by single quotes at the beginning and end of the three values that make it up:

$consulta= " Insert into Patient(Patient_Name,Patient_LastName,Patient_Age,Patient_DOB) values ('{$name}','{$lastName}','{$age}','{$yyyy}-{$mm}-{$dd}')";

However, your code is vulnerable to SQL injection attacks. To neutralize it you can use prepared queries.

I take this opportunity to introduce some improvements:

<?php 

    $name=     ( empty($_GET['name']) )     ?  NULL  : $_GET['name'];
    $lastName= ( empty($_GET['lastName']) ) ?  ""    : $_GET['lastName'];
    $age=      ( empty($_GET['age']) )      ?  0     : $_GET['age'];
    $dd=       ( empty($_GET['dd']) )       ?  00    : $_GET['dd'];
    $mm=       ( empty($_GET['mm']) )       ?  00    : $_GET['mm'];
    $yy=       ( empty($_GET['yy']) )       ?  0000  : $_GET['yy'];
    $dob="$yy$mm$dd";

    if ($name){

        if( $conexion=mysqli_connect($hostname,$username,$password,$database) ){
            /*
                *En las consultas preparadas los valores 
                *que irían directamente a ejecución 
                *son cambiados por marcadores (?)
                *porque esos valores pueden ser manipulados 
                *para introducir código dañino
            */
            $sql= "INSERT INTO Patient(Patient_Name,Patient_LastName,Patient_Age,Patient_DOB) 
                   VALUES (?,?,?,?)";
            if ($stmt = mysqli_prepare($conexion, $sql)) {
                /*
                    *Es aquí donde se pasan los valores al manejador
                    *el cual se encarga de sanearlos y neutralizar
                    *cualquier intento de inyección
                    *Las letras "ssis" indican de qué tipo es el dato 
                    *(s) es cadena o string, (i) es entero o integer
                    *Los valores tienen que ir en el orden en que están
                    *en la consulta $sql
                */
                mysqli_stmt_bind_param($stmt, "ssis", $name,$lastName,$age,$dob);
                if( mysqli_stmt_execute($stmt) ) {
                    $msg=printf("Se insertaron %d filas", mysqli_stmt_affected_rows($stmt));
                }else{
                    $msg=printf("Error: %s ejecutando la consulta", mysqli_stmt_error($stmt));
                }
                mysqli_stmt_close($stmt);
            }else{
                    $msg=printf("Error: %s preparando la consulta", mysqli_error($conexion));
            }
            mysqli_close($conexion);
        }else{
            $msg=printf("Error de conexión: %s", mysqli_connect_error());
        }
    }else{
        $msg="El nombre estaba vacío";  
    }
    echo $msg;
?>

I have put some comments inside the code, in the most neuralgic parts.

Strict control has also been established, so that you always report what happened. Your original code was mudo in some ways: if the query failed by duplicate key, or by syntax error, or because the connection was not available. In none of these cases would you know if the query was executed or not and what was the result of it.

I hope you find it useful.