Send NULL to the BD field

Navigation
0

I'm doing an exercise with PHP and MySQL where I make a form with a series of data. The telephone field allows null, so the user leaves it blank in the database I want it to be saved as NULL but I left a blank space.

Image of the BD:

PHP Code: 

<?php
    $correo = $_POST["correo"];
    $nombre = $_POST["nombre"];
    $password = $_POST["password"];
    if(isset($_POST["telefono"]))   
        $telefono = $_POST["telefono"];      
    else
        $telefono = NULL;

    if(isset($_POST["publicidad"]))
        $publicidad = 1;     
    else
        $publicidad = 0;            

    $conexion = mysqli_connect("localhost","root","","bdusuarios");//Conexion BD

    $consulta = "INSERT INTO usuarios VALUES(DEFAULT,'".$correo."','".$nombre."','".$password."',DEFAULT,".$publicidad.",'".$telefono."');";//ConsultaSQL

    $consulta_correo = "SELECT * FROM usuarios WHERE correo = '".$correo."'"; //Consulta para comprobar el correo

    $resultado = mysqli_query($conexion,$consulta_correo);//Comprobacion del correo

    if(mysqli_num_rows($resultado) == 0)//Si la consulta del correo es 0, es porque no existe ese correo en la BD y se puede registrar
    {
        if(mysqli_query($conexion,$consulta))//Si la consulta es correcta...
        {
            echo "<h3>Usuario registrado correctamente</h3>";
            echo "Filas afectadas: ".mysqli_affected_rows($conexion);;
        }
        else
        {
            echo "Error al registrar el usuario";
        }
    }
    else////Si la consulta del correo es distinto de 0, es porque ya hay un usuario con ese correo
    {
        echo "<h3>Ya existe un usuario con ese correo</h3>";
    }
?>
    
asked by Mario Guiber 18.10.2017 в 20:16
source

5 answers

1

There is the function NULLIF() in MySQL, it receives two parameters and if both match the function returns NULL otherwise it prints the first parameter as it arrived. For example: 

"INSERT INTO frutas SET nombre = NULLIF($nombre,'');"

If the Name field was empty, the variable $nombre will contain an empty string, as we specify that you wait for the function NULLIF , so you will insert in the database a NULL , but if he had written Apple inserted Apple.

    
answered by 18.10.2017 / 21:06
source
1

Good morning,

Keep in mind that the query is a string.

Therefore, you should use: 

if(isset($_POST["telefono"]))   
    $telefono = $_POST["telefono"];      
else
    $telefono = "NULL";
    
answered by 18.10.2017 в 20:22
0

First you have to verify that this column admits values NULL , otherwise the code will fail.

Secondly, the correct way to enter null values is to enter this NULL and not this 'NULL' .

If you write: 

INSERT INTO usuarios (telefono) VALUES ('NULL');

What you will do is enter a string that says NULL in the column.

On the other hand, if you write: 

INSERT INTO usuarios (telefono) VALUES (NULL);

You will be actually entering a NULL value in the column.

Finally, for the future, consider securing your queries against SQL injection using prepared queries.

    
answered by 18.10.2017 в 20:47
0

your problem is when putting together the query 

$consulta = "INSERT INTO usuarios VALUES(DEFAULT,'".$correo."','".$nombre."','".$password."',DEFAULT,".$publicidad.",'".$telefono."');";//ConsultaSQL

As you can see, you are placing quotation marks at the value so be it empty '".$telefono."' One solution is 

$telefono = trim($_POST["telefono"] != "") ? "'".$_POST["telefono"]."'" : NULL;

and in the query 

$consulta = "INSERT INTO usuarios VALUES(DEFAULT,'".$correo."','".$nombre."','".$password."',DEFAULT,".$publicidad."","
.$telefono.")";

PHP I post the obsolteas the libraries mysql I recommend you read more about   SQL Inyection

    
answered by 18.10.2017 в 20:47
0

If you send the empty strings to the instruction, the empty string will be saved to save the default value, you can omit the value by changing your syntax and specifying the fields you want to save.

for your example you can validate the value of the string before sending it. 

$correo = !empty($correo) ? "'" . $correo . "'" : 'null';
$nombre = !empty($nombre) ? "'" . $nombre . "'" : 'null';
$password = !empty($password) ? "'" . $password . "'" : 'null';
$telefono = !empty($telefono) ? "'" . $telefono . "'" : 'null';
$consulta = "INSERT INTO usuarios VALUES(null,".$correo.",".$nombre.",".$password.",null,".$publicidad.","
.$telefono.")";
    
answered by 19.10.2017 в 00:26
Error in html form to php IONIC- No 'Access-Control-Allow-Origin' header is present