SQL & PHP - Select everything using where

Question

SQL & PHP - Select everything using where

Navigation

#1 by (3 votes)
#2 by (2 votes)

1

I'm doing a very simple filtering system using PHP and mysql .

$stmt = $conexao->prepare("select * from filme where genero = '$genero'");

I'm wanting to put one in the genre list which, when selected, displays all the movies.

I know I could do this easily using if / else:

if ($genero == "todos"){
   $stmt = $conexao->prepare("select * from filme");
}else{
   $stmt = $conexao->prepare("select * from filme where genero = '$genero'");
}

But I wanted to know if you have any other way to do this if the system gets much more complex and if / else gets too big. For example, if you wanted to filter the movies also by year of release, director and indicative rating, all at the same time and with the same option of displaying all.

How could I do this?

Thanks in advance for the answers.

php mysql

asked by anonymous 11.12.2018 / 21:38

2 answers

read txt file with less than 5 elements using read.table How to create a list and put other lists in?

score 3 · Answer 1

If you wanted to filter the movies also by year of release, director and indicative rating, all at the same time and with the same option of displaying all

If you want to do everything together, it would look something like this:

$stmt = $conexao->prepare("select * from filme where genero = '$genero' and lancamento = '$lancamento' and diretor = '$diretor' and classificacao = '$classificacao'");

You can even mount this query more dynamically:

// Exemplos:
$genero = 'acao';
$lancamento = '2018';
$diretor = 'Stallone';
$classificacao = 18;

$sql = "select * from filme where ";
if(isset($genero))
    $buscando[] = "genero = '".$genero."'";
if(isset($lancamento))
    $buscando[] = "lancamento = '".$lancamento."'";
if(isset($diretor))
    $buscando[] = "diretor = '".$diretor."'";
if(isset($classificacao))
    $buscando[] = "classificacao = '".$classificacao."'";

$sql .= join(' and ', $buscando);
$sql .= ';';

$stmt = $conexao -> prepare($sql); // select * from filme where genero = 'acao' and lancamento = '2018' and diretor = 'Stallone' and classificacao = '18';

score 2 · Answer 2

You can automate this by using $ _GET or by mounting an array:

Example:

$filtro = ["ano" => "2018", "diretor"=> "Stalonne"];
$where = "";
foreach($filtro as $key => $value){
    $where .= $key." = '".$value."' AND ";
}
$where = substr($where, 0, -4);
$stmt = $conexao->prepare("select * from filme ".$where);

This string would look like this:

select * from filme ano = '2018' AND diretor = 'Stalonne'

This makes it even easier to refactor when needed.