Mysql - Query in related tables

Question

The scenario is simple, SQL displays all the criteria results and there is a filter to select multi values that will be passed to SQL, which should refer to tbl2 in the field tbl2_criterio, and thus display only records that contain values equal to filter values.

I tried to accomplish in the $where_filtro the use of "AND tbl2_criterio = '" . $var_filtro[0] . "' and the others, but it didn’t work.

How to check the records on tbl2 in the field tbl2_criterio and adapt to SQL?

Follow down what I’ve tried so far:

<?php
$where_filtro = ""; // Vazio na QUERY se $_GET['var_filtro'] não foi definido.

if( isset( $_GET['var_filtro'] ) {

    $var_filtro = $_GET['var_filtro'];
    $var_filtro = explode(",",$var_filtro);
    // Imprime: 
    // $var_filtro[0] = "valor1";
    // $var_filtro[1] = "valor2";
    // $var_filtro[2] = "valor2";

    for($i=0;$i<=count($_GET['var_filtro']);$i++) {
        $where_filtro .= " AND `tbl2_criterio` = '" . mysqli_real_escape_string($conn,$var_filtro[$i]) . "' ";
    }

} 

$sql = "SELECT 
            * 
        FROM 
            `tbl1` 
        INNER JOIN 
            `tbl2` 
        ON 
            `tbl2_id_pai` = `tbl1_id` 
        WHERE 
            `tbl1_status` = 'A' 
        " . $where_filtro . "
        ;";

$query = mysqli_query( $conn, $sql );

while( $row = mysqli_fetch_array( $query, MYSQLI_ASSOC ) {
    echo $row['tbl1_row1']."<br>\n";
}
?>

SQL looks like this after running when I echo into $sql.

SELECT 
    * 
FROM 
    `tbl1` 
INNER JOIN 
    `tbl2` 
ON 
    `tbl2_id_pai` = `tbl1_id` 
WHERE 
    `tbl1_status` = 'A' 
AND 
    `tbl2_criterio` = 'valor1' 
AND 
    `tbl2_criterio` = 'valor2' 
AND 
    `tbl2_criterio` = 'valor3';

Query returns: Mysql did not return any record. (Query took 0.0013 seconds.).

But there are values in the records and they are related to the record of tbl1.

Answer 1

This way it should serve you...

Note that the field tbl2_criterio is one, and in your SQL you are playing it several times on where, then you ask SQL to result only the records that the field tbl2_criterio be equal to valor1, valor2 and valor3 at the same time... For your case you would have to use the clause OR instead of AND or as in my example, for a cleaner way, use the IN()

<?php
  if(isset( $_GET['var_filtro'])
  {
      $var_filtro = $_GET['var_filtro'];
      $var_filtro = explode(",",$var_filtro);
      // Imprime: 
      // $var_filtro[0] = "valor1";
      // $var_filtro[1] = "valor2";
      // $var_filtro[2] = "valor2";


      $where_filtro = " AND T2.tbl2_criterio in (";

      for($i=0;$i<=count($_GET['var_filtro']);$i++) 
      {
          if($i == 0)
            $where_filtro .= "'" . mysqli_real_escape_string($conn,$var_filtro[$i]) . "'";
          else
            $where_filtro .= ",'" . mysqli_real_escape_string($conn,$var_filtro[$i]) . "'";
      }

      $where_filtro .=  ")";
  } 

  $sql = "SELECT * 
          FROM       tbl1 T1
          INNER JOIN tbl2 T2 ON T2.tbl2_id_pai = T1.tbl1_id
          WHERE T1.tbl1_status = 'A' " . $where_filtro . ";";

  $query = mysqli_query($conn, $sql);

  while( $row = mysqli_fetch_array( $query, MYSQLI_ASSOC ) 
  {
      echo $row['tbl1_row1']."<br>\n";
  }
?>

Note: Sorry for PHP errors, not my strong suit!

Answer 2

Try it this way:

$where = "";
if(isset($_GET['var_filtro']){
    $var_filtro = $_GET['var_filtro'];
    $var_filtro = explode(",",$var_filtro);
    if(count($var_filtro)>0){
        $where .= "LEFT JOIN tbl2 ON tbl2.tbl2_id_pai=tbl1.tbl1_id";
        for($i=0;$i<=count($var_filtro);$i++) {
            $where .= " AND tbl2.tbl2_criterio='".$var_filtro[$i]."' ";
        }
    }
}


$consulta = mysqli_query($conn, "
    SELECT * FROM tbl1
    {$where}
    WHERE tbl1.tbl1_status = 'A'
");