mysqli_query returns false to select done with REGEXP

Asked

Viewed 398 times

2

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.

  • Your research consists of a group of words: Example: nome1 nome 2 and then you make one item at a time? Why don’t you use the OR ? Type: .*nome1*|.*nome2*?

  • The code is part of a php page that has the function of searching for a user. It would be similar to the facebook text box, where you type in someone’s name and various results appear. Only here I did something basic, just to see it actually working. It takes what the user typed in the text box and separates all the words typed and separated by a space. Then he should take all the separate words and test them, saving all the rows in the table of the bd whose column 'name' equals $string, or where $string is a substring of the name. Only I haven’t been able to test it yet.

1 answer

4


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:

Mysqli:

Understand the criticisms here as suggestions for you to improve.

  • Thanks for the advice. I will try to put them into practice. However, I rewrote the code by replacing while(mysqli_fetch_assoc($busca) != false) for while ($dados = mysqli_fetch_assoc($busca)) and it still didn’t work. Using mysqli_num_rows(), i saw that the query does not actually return any table row. I will continue looking for some case similar to mine.

  • I used your code, but I noticed there’s something wrong with the lines array_unshift($params, $stmt); and call_user_func_array('mysqli_stmt_bind_param', $params);. When using the first, the $stmt is viewed as an invalid parameter for the myslqi_stmt_bind_param(), which gives me the following warning: Warning: Wrong parameter count for mysqli_stmt_bind_param() in procurar.php on line 47. In addition, this same function myslqi_stmt_bind_param() is not receiving values to be replaced by '?' in mysqi_prepare(). Then I comment if I can correct this.

  • Now it worked. However, the program does not find any results in db, printing 'Array()'.

Browser other questions tagged

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