Query SQL with multiple WHERE conditions

Question

I was trying to make a search system that searched in 2 fields of the table at the same time and could have several conditions in SELECT WHERE, I searched in several places and found this query, but when I went to run it gave the error:

"Fatal error: Call to a Member Function fetch_array() on Boolean".

Can someone help me identify the problem please?

Answer 1

The mysqli_query returns an object in case of success and if it fails returns a Boolean FALSE, that is, very likely that your $resultado be it false and you’re trying to make a fetch_array() in a Boolean (call to a Member Function on Boolean).

What you can do to help debug this is to make a :

if (!$resultado) {
   // faz um tratamento de erro pra saber qual erro deu
   // e retorna algo para parar a execução deste script
}

And then you’ll just do $resultado->fetch_array() case $resultado not be FALSE.

Answer 2

Your query is not a valid query, so the return of mysqli_query is a BOOLEAN, in this case false.

You have other code problems. You close the loop here foreach ($v as $key => $value);, instead of adding more conditions in the query OR for each item in $pesquisa.

I think what you want is to have multiple possibilities for descricao and grau depending on what was searched, type algo para pesquisar would create 3 combinations, one for each word.

$pesquisa = $_POST["pesquisa"];
$v = explode(" ", $pesquisa);

$condicoes = []; // um array para guardar todas as condicoes
foreach ($v as $key => $value) {
    // adicionar ao array uma condicao por cada valor passado em $pesquisa
    $condicoes[] = "descricao LIKE '%{$value}%' OR grau LIKE '%{$value}%'";
}

$sql = "SELECT * FROM lentes WHERE " . implode(" OR ", $condicoes);

$resultado = mysqli_query($con, $sql);

// ... o resto do teu código

---

How it works

PHP eats arrays for breakfast, so it’s one of the most useful data structures in the language, especially when you’re using a more procedural code style, even when you use objects it’s useful, I would suggest getting familiar with what’s possible by reading PHP documentation about arrays.

The code I put on foreach just adding items to the array, for example, imagine that $pesquisa is algo para pesquisar.

// aqui dividimos a string "algo para pesquisar" em cada espaço
$v = explode(" ", $pesquisa);

// isto cria um array assim
// Array
// (
//     [0] => algo
//     [1] => para
//     [2] => pesquisar
// )

// por cada palavra, adicionamos um item ao nosso array condicoes
$condicoes = []; 
foreach ($v as $key => $value) {
    $condicoes[] = "descricao LIKE '%{$value}%' OR grau LIKE '%{$value}%'";
}

// $condicoes, depois deste ciclo será
// Array
// (
//     [0] => descricao LIKE '%algo%' OR grau LIKE '%algo%'
//     [1] => descricao LIKE '%para%' OR grau LIKE '%para%'
//     [2] => descricao LIKE '%pesquisar%' OR grau LIKE '%pesquisar%'
// )

We can use the implode(), which is the opposite of explode(), to turn $conditions into a string, and even we can (must! ) check for $conditions (ie if something has been added to the array.

$sql_condicoes = ""; // o SQL das condições é vazio caso não existam condicoes
if (count($condicoes) > 0) {
    $sql_condicoes = " WHERE " . implode(" OR ", $condicoes);
}

$sql = "SELECT * FROM lentes" . $sql_condicoes;