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

Asked

Viewed 352 times

0

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)

sql

  • but then you need to pass the keyword and the date... ie, you mount a first condition. if($date) $condition = "AND data = x"; Else $condition = ""; and passes the condition within the Where

  • the idea is: to search for all records that have the specified condition, if there is no condition, it will not even go through the AND condition=x, so it will not look for the NULL. And you tested to see the result?

  • 1

    @André Baill Testei, I put some changes in perrgunta.

  • NULL is a special value. You need to use IS NULL or IS NOT NULL tests.

1 answer

0

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}";

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.