SQL & PHP - Select everything using Where

Asked

Viewed 129 times

1

I’m making a very simple filter system using PHP and mysql.

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

I’m wanting to put one on the list of genres that, when selected, displays all the movies.

I know that I could easily do this 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 there is 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 films also by year of release, director and indicative rating, all at the same time and with the same option to display all.

How could I do that?

Thanks for your answers

php mysql

  • What you can do is concatenate the query while the filter is selected. If there is no filter it will select from the basic select only

  • @gabrielfalieri I did not understand very well, could you explain me better?

  • Breno, let’s assume. How do you know that the "generous" has been selected?

  • In html there is a select tag with all genres registered, so when the value is changed the program executes an ajax that passes it by url, at least that’s what I did

2 answers

3

if you wanted to filter the films also by year of release, director and indicative rating, all at the same time and with the same option to display all

If you want to do all together, 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 still ride that 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';

  • thanks, it helped a lot

2

You can automate this 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);

That string would look like this:

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

This way it is easier even to refactoring when necessary.

Browser other questions tagged php mysql

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