Fix Where Clause with like null operator that finds data with null parameters

Question

I have a query that is picking up the fields null with the like operator. Let’s see an example in the query below:

$palavraChave = null;
$consulta = "select * from receitas where receita like '%$palavraChave%' ";  

Note that if I set the $palavraChave variable to null, it still takes all the data from the database, including ignoring the Where clause. I think it’s a mistake, it shouldn’t include the null condition.

Here comes the question: why are you doing this? Because I have two fields and one of the two may be empty.

I want to search by date and keyword. If the date is null, the only search parameter will be the keyword. If this is not the case, only the date will serve as a search condition. And in the error I am referring to, you are taking the data regarding the field filled as to the null field, that is, it brings the two conditions, regardless of null or not. In short, I just want what I ask.

Follow a slightly different code, with some changes:

$_POST['data']='2015-11-15'; $_POST['word']='inss';

$condicao = "where id_recipe != '' ";

if($_POST['date']){
$condition .= " AND data = '". $_POST['date']."'";
}

if($_POST['word']){
$condicao .= " AND LIKE word = '". $_POST['word']."'";
}

$query = "SELECT * FROM recipes {$condition}";

$query=mysql_fetch_array($query, $server);

?>

In this change, you don’t find the first argument (the $query)

Answer 1

You can elaborate as follows:

$condicao = "WHERE id != '' ";

if($_POST['data']){
    $condicao .= " AND data = '".$_POST['data']."'";
}

if($_POST['palavra']){
    $condicao .= " AND LIKE palavra = '".$_POST['palavra']."'";
}

$consulta = "SELECT * FROM receitas {$condicao}";