Error Search PHP and Mysql Database

Question

I’m making a page that searches for information in tables of a Mysql database, the connection is beautiful, but is showing two errors :

Warning: mysqli_stmt::bind_param(): Number of variables doesn’t match number of Parameters in Prepared statement in **/home/----/public_html/search.php on line 7

Warning: mysqli_fetch_array() expects Parameter 1 to be mysqli_result, Object Given in **/home/---/public_html/search.php on line 15

My php code looks like this:

<?php

include ('conecta.php');

$pesquisa_rapida = $_POST["txtpesquisa"];
$sql=$mysqli->prepare("SELECT * FROM tabela WHERE nm_candidato LIKE '%".$pesquisa_rapida."%'");
$sql->bind_param("s",$pesquisa_rapida);

$sql->execute();
$sql->store_result();
$result=$sql->affected_rows;

if ($result > 0){

        while($linha = mysqli_fetch_array($sql)){
           $nm_candidato = $linha['nm_candidato'];        
           $nm_candidatura = $linha['nm_candidatura']; 
           $ds_cargo = $linha['ds_cargo'];
           echo "<strong>Nome: </strong>".@$nm_candidato;
           echo "<br /><br />"; 
           echo "<strong>Nome Candidatura: </strong>".@$nm_candidatura;
           echo "<br /><br />"; 
           echo "<strong>Cargo: </strong>".@$ds_cargo;
           echo "<br /><br />"; 
        }
}
else {
    echo "Desculpe, nada foi encontrado";
}    
?>

Answer 1

Your code makes the bind of a value but no placholder(?) was passed in consultation, also jokers(% or _) in the variable with term to be searched and not in sql. The second error is caused by the first to fix this and vitar sql Injection swap the variable for a query:

Change:

$sql=$mysqli->prepare("SELECT * FROM tabela WHERE nm_candidato LIKE '%".$pesquisa_rapida."%'");

To:

$pesquisa_rapida = '%'. $_POST["txtpesquisa"] .'%';
$sql=$mysqli->prepare("SELECT * FROM tabela WHERE nm_candidato LIKE ?");
$sql->bind_param("s",$pesquisa_rapida);
$res = $sql->get_result();
$result=$sql->affected_rows;

while($linha = $res->fetch_assoc()){
   //demais linhas
}

Answer 2

Change the line:

while($linha = mysqli_fetch_array($sql)){

To

while($linha = mysqli_fetch_array($result)){

Why don’t you use num_rows?

Answer 3

Thank you for all your help. That’s how it is:

<?php

    include ('conecta.php');

    $pesquisa = '"%'. $_POST["txtpesquisa"] .'%"';

    $connection = mysqli_connect($host, $user, $pass,$database);
     if (!$connection) {
     echo ("Erro ao conectar!"); 
    }

    $query = "SELECT * FROM tabela "
            . "WHERE nome LIKE ".$pesquisa."";
    $result =  mysqli_query($connection,$query);
    if (!$result) {
      die("Query invalida");
    }

    while ($linha=  mysqli_fetch_array($result,MYSQLI_ASSOC)) {

    if ($result > 0){
        echo "Nome: $linha[nome]<br />"; 
        echo "<hr>";

    }
    else {
        echo "nada foi encontrado";
    }
    }
   ?>