Problems to execute query (SELECT with INNER JOIN) that uses data from an HTML form

Question

I have the following code below:

public function listarClientesBuscadosDoForm()
{   
    try
    {                       
        $c = new Conexao();
        $db = $c->conectar();

        $query = "SELECT a.cod, a.razao_social, a.cnpj, a.inscricao_estadual, a.servico, 
        b.tributacao, c.atividade_economica FROM clientes AS a 
        INNER JOIN tributacoes AS b ON a.id_tributacao = b.id
        INNER JOIN atividade_economica AS c ON a.id_atividade_economica = c.id
        WHERE a.razao_social LIKE :razao_social AND a.cnpj LIKE :cnpj
        AND a.inscricao_estadual LIKE :inscricao_estadual AND a.id_tributacao
        LIKE :id_tributacao AND a.id_atividade_economica LIKE :id_atividade_economica";

        $stmt = $db->prepare($query);                   
        $stmt->bindValue(':razao_social',"%". $this->razao_social ."%");
        $stmt->bindValue(':id_tributacao ',"%". $this->tributacao ."%");                                                    
        $stmt->bindValue(':cnpj',"%". $this->cnpj ."%");
        $stmt->bindValue(':inscricao_estadual',"%". $this->inscricao_estadual ."%");
        $stmt->bindValue(':id_atividade_economica',"%". $this->id_atividade_economica ."%");                    
        $stmt->execute();

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);    

        echo json_encode($result);

   } catch(PDOException $e) {
                    echo "Erro: ".$e->getCode()." Mensagem: ".$e->getMessage();             
   }

}

Would you recommend a correct way to perform this type of consultation ? This data that is passed to the query is coming from a query form (HTML) and they can come empty as well (correct is if it comes empty, bring all the data). The problem is that I use 3 tables and Inner Join in this query.

Below is the description of the 3 tables:

customers

tributary

activity

Form (HTML):

Would you have any idea of the correct way for me to perform this query or build this code ?

Answer 1

I did something that doesn’t seem to be the best way, but it was something that solved my problem. In my view it solved in an improbable and wrong way (but solved).

I had to take out the binds and put a very bizarre IF sequence there.

My intention was to keep the Binds and do it the right way, so I opened the question here. But as I did not get an answer, I decided to do so (I was in a bit of a hurry).

But if an answer comes up with the right way to do it, I’ll accept the same.

Code:

public function listarClientesBuscadosDoForm()
{   
   try
   {                        
       $c = new Conexao();
       $db = $c->conectar();

   $razao_social = "";
   $cnpj = "";
   $inscricao_estadual = "";
   $tributacao = "";
   $atividade_economica = "";                   

   if($this->razao_social <> "")
   {
        $razao_social = " AND a.razao_social = '$this->razao_social'";                      
   }                    

   if($this->cnpj <> "")
   {
        $cnpj = " AND a.cnpj = '$this->cnpj'";
   }

   if($this->inscricao_estadual <> "")
   {
        $inscricao_estadual = " AND a.inscricao_estadual = '$this->inscricao_estadual'";
   }

   if($this->tributacao <> "")
   {
        $tributacao = " AND a.id_tributacao = '$this->tributacao'";
   }

   if($this->id_atividade_economica <> "")
   {
        $atividade_economica = " AND a.id_atividade_economica = '$this->id_atividade_economica'";
   }                    

   $query = "SELECT a.cod, a.razao_social, a.cnpj, a.inscricao_estadual, a.servico, 
             b.tributacao, c.atividade_economica FROM clientes AS a 
             INNER JOIN tributacoes AS b ON a.id_tributacao = b.id
             INNER JOIN atividade_economica AS c ON a.id_atividade_economica = c.id
             WHERE a.id IS NOT NULL $razao_social $cnpj $inscricao_estadual 
             $tributacao $atividade_economica ORDER BY a.razao_social";

   $stmt = $db->prepare($query);                                    
   $stmt->execute();

   $result = $stmt->fetchAll(PDO::FETCH_ASSOC); 

   echo json_encode($result);               
   } catch(PDOException $e) {
        echo "Erro: ".$e->getCode()." Mensagem: ".$e->getMessage();             
   }
}