Define variable as null in a Query in php

Question

Define variable as null in a Query in php

Navigation

#1 by (1 votes)

0

I have the following QUERY on my system:

$Query = "SELECT * FROM Monit WHERE
    ($Modulo IS NULL OR Modulo = '$Modulo') AND 
    ($Usuario IS NULL OR Usuario = '$Usuario') AND
    ($Data IS NULL OR Data_reg = '$Data') 
    ORDER BY Data_monit DESC LIMIT 0,10";
    $execute = mysqli_query($conexao, $Query);

    while($line = mysqli_fetch_array($execute)){
        echo $line['Modulo'];
    }

To test if it would work when entering some value NULL set variables with these values:

$Modulo = "NULL";
$Usuario = "NULL";
$Data = "NULL";

And it works perfectly this way. However, if I define the entries this way:

$Modulo = "NULL";
$Usuario = "roberto";
$Data = "NULL";

It does not work because it's missing the single quotes '' ' If I put it this way:

$Modulo = "NULL";
$Usuario = "'roberto'";
$Data = "NULL";

The query does not work, and I add the single quotes in the query:

$Query = "SELECT * FROM Monit WHERE
    ($Modulo IS NULL OR Modulo = '$Modulo') AND 
    ('$Usuario' IS NULL OR Usuario = '$Usuario') AND
    ($Data IS NULL OR Data_reg = '$Data') 
    ORDER BY Data_monit DESC LIMIT 0,10";
    $execute = mysqli_query($conexao, $Query);

    while($line = mysqli_fetch_array($execute)){
        echo $line['Modulo'];
    }

It will work, however, if you return the NULL value in the variables it will not return anything; I do not know if it was very clear, but basically I do not know how to define the variables that will be in the query as null.

php mysql query null

asked by anonymous 20.02.2018 / 15:34

1 answer

Set Enabled ActionList Property to True Debug Delphi keeps stopping even after breakpoint removal?

score 1 · Accepted Answer

I do not know if it is a very elegant solution, but you can validate if the variable is filled before mounting the query and keep the single quotation marks :

if($Modulo == NULL) $Modulo = '';
if($Usuario == NULL) $Usuario = '';
if($Data == NULL) $Data = '';

$Query = "SELECT * FROM Monit WHERE
    ($Modulo = '' OR Modulo = '$Modulo') AND 
    ($Usuario = '' OR Usuario = '$Usuario') AND
    ($Data = '' OR Data_reg = '$Data') 
    ORDER BY Data_monit DESC LIMIT 0,10";
    $execute = mysqli_query($conexao, $Query);