mysqli_query returns false to select done with REGEXP

Question

I am having the following problem: I am trying to capture a table row of my database using a regex and, when entering the code in the Mysql terminal, it captures the line normally. Already in my php code, mysqli_query() always returns false with these same commands. The code follows below:

<?php
session_start();
if(isset($_POST['pesquisa'])){
    $palavras_pesquisa = explode(' ',$_POST['pesquisa']);
    $conn = mysqli_connect('localhost','root','','secure_login');
    $rows = array();    
    $ids = array();
    for($i = 0; $i < count($palavras_pesquisa) ; $i++){
    $string = '.*'.$palavras_pesquisa[$i].'*';
    $busca = mysqli_query($conn,"SELECT * FROM usuarios WHERE nome REGEXP '$string'");

//'$busca' sempre retorna false, fazendo com que o restante abaixo não seja executado. 

while(mysqli_fetch_assoc($busca) != false){
        array_push($rows,mysqli_fetch_assoc($busca));
    }
    }

    for($i = 0;$i < count($rows);$i++){
        if(in_array($rows[$i]['id'],$ids)){
            unset($rows[$i]);
        }else{
            array_push($ids,$rows[$i]['id']);
            ?>
                <p><?php echo $rows[$i]['nome']; ?></p>
                <p><?php echo $rows[$i]['email']; ?></p>
                <p><?php echo $rows[$i]['interesses']; ?></p>
            <?php
        }
    }
}?>

Like I said before, SELECT * FROM usuarios WHERE nome REGEXP '$string' works perfectly on the mysql terminal. I wonder if I’m doing something wrong here. I thank you already.

Answer 1

First, every time you use the mysqli_fetch_assoc it will return the next result, ie at the moment it does this:

while(mysqli_fetch_assoc($busca) != false){
    array_push($rows,mysqli_fetch_assoc($busca));
}

You run two mysqli_fetch_assoc each loop, that is to say if your query only this bringing a result by loop for($i = 0; $i < count($palavras_pesquisa) ; $i++){ then it will always merge values NULL, something else you compared to FALSE in mysqli_fetch_assoc($busca) != false, which makes no sense, because mysqli_fetch_assoc returns or array or null only, also use the mysqli_error to detect errors.

The right thing to do:

$busca = mysqli_query($conn,"SELECT * FROM usuarios WHERE nome REGEXP '$string'") or die(mysqli_error($conn));

//'$busca' sempre retorna false, fazendo com que o restante abaixo não seja executado.

while ($dados = mysqli_fetch_assoc($busca)) {
    array_push($rows, $dados);
}

In short, ALWAYS DO EXACTLY AS THEY ARE IN THE DOCUMENTATION EXAMPLES (generally adoc in English is more correct in most cases), even if they are not great, still is better than using wrong, or using randomly without knowing what is doing, the effect of mysqli_fetch_assoc is similar to yeld, every time you use mysqli_fetch_assoc he will bring the next line.

However make a SELECT for every loop can be wasted, you could simplify doing this:

for($i = 0; $i < count($palavras_pesquisa) ; $i++){
$string = '.*'.$palavras_pesquisa[$i].'*';
$busca = mysqli_query($conn,"SELECT * FROM usuarios WHERE nome REGEXP '$string'");

Prefer to use implode and do only one query, thus:

<?php
session_start();
if(isset($_POST['pesquisa'])){
    $palavras_pesquisa = explode(' ',$_POST['pesquisa']);
    $conn = mysqli_connect('localhost','root','','secure_login');
    $rows = array();
    $ids = array();

    if (!empty($palavras_pesquisa)) { //Verifica se tem palavras

        foreach ($palavras_pesquisa as &$value) {
            $value = '.*' . $value . '*';
        }

        $consulta = "nome REGEXP '" . implode("' OR nome REGEXP '", $palavras_pesquisa) . "'"; //Cria um OR pra cada palavra

        $busca = mysqli_query($conn, "SELECT * FROM usuarios WHERE " . $consulta) or die(mysqli_error($conn));

        while ($dados = mysqli_fetch_assoc($busca)) {
            $rows[] = $dados;
        }
    }

    for($i = 0;$i < count($rows); $i++){
    ...

Something else, ALWAYS MAKE A GOOD INDENTATION OF THE CODE, This helps not to get lost.

However it is also recommended not to pass data directly in the query, this is due to sqlinjection, if you are using mysqli then prefer to use the Prepared statement, would look like this:

<?php
session_start();
if(isset($_POST['pesquisa'])){
    $palavras_pesquisa = explode(' ', $_POST['pesquisa']);

    $conn = mysqli_connect('localhost', 'root', '', 'secure_login');
    $rows = array();
    $ids = array();

    if (!empty($palavras_pesquisa))//Verifica se tem palavras
    {
        //Gera where
        $consulta = rtrim(str_repeat('`nome` REGEXP ? OR ', count($palavras_pesquisa)), 'OR ');

        //Gera tipos parâmetros
        $paramsTypes = str_repeat('s', count($palavras_pesquisa));

        //Gera referencias
        $params = array();
        $params[] = &$paramsTypes;

        foreach ($palavras_pesquisa as $value) {
            $value = '.*' . $value . '*';
            $params[] = &$value;
        }

        $stmt = mysqli_prepare($conn, "SELECT * FROM `usuarios` WHERE " . $consulta);

        //Adiciona o stmt como primeiro item no array
        array_unshift($params, $stmt);

        if ($stmt) {
            //Passa array como parâmetros no bind_param
            call_user_func_array('mysqli_stmt_bind_param', $params);

            if(!mysqli_stmt_execute($stmt)) {
                echo 'Erro no stmt:', mysqli_stmt_error($stmt);
                exit;
            }

            $result = mysqli_stmt_get_result($stmt);

            while ($dados = mysqli_fetch_assoc($result))
            {
                $rows[] = $dados;
            }

            mysqli_stmt_close($stmt);
        } else {
            echo 'Erro no prepare:', mysqli_error($conn);
            exit;
        }
    }

    for($i = 0;$i < count($rows); $i++){
    ...

See the documentation to understand the functions I used in the last example:

• call_user_func_array
• rtrim
• str_repeat

Mysqli:

• mysqli_prepare
• mysqli_stmt_execute
• mysqli_stmt_get_result

Understand the criticisms here as suggestions for you to improve.