Use explodes to separate categories

Question

good afternoon how could I use the explode in this snippet of code for it to have more than one category per movie? I appreciate the help

$categoria = $_GET['category'];
$sql = "SELECT * FROM filme WHERE categoria IS NOT NULL AND categoria LIKE '%$categoria%' ORDER BY nome LIMIT 25";

the variable $categoria she is a get and is being called at index in href with ? Category=action or if it is not action films would be in other href ? Category=adventure

Answer 1

Although what you are doing is subject to SQL Injection, you can use the implode() to transform an array to a string.

Suppose your $_GET is like this:

http://site.com.br/filmes?category=acao&category=romance&category=thriller

You could do something like this:

$categoria = $_GET['category'];
$likes = implode("%' OR categoria LIKE '%",$categoria);
/* esse implode faz um array assim:
  [0] => 'acao',
  [1] => 'romantico',
  [2] => 'thriller'

  ficar assim:

  "acao%' OR categoria LIKE '%romantico%' OR categoria LIKE '%thriller"
*/

$sql = "SELECT * FROM filme WHERE categoria IS NOT NULL AND ( categoria LIKE '%$likes%' ) ORDER BY nome LIMIT 25";

The explode() takes a string and becomes an array, for example:

$str = "este,e,um,string";
$array = explode(",",$str);
//devovle:
/*
  [0] => "este",
  [1] => "e",
  [2] => "um",
  [3] => "string"
*/

So to use the explode(), the $_GET would have to be like this:

http://site.com.br/filmes?category=acao,romance,thriller

And then the only thing that changes is:

$categoria = explode(',', $_GET['category']);

Elaboration

It has been clarified that your problem is the fact that acao also returns animacao due to the same letters acao.

In this case, you would have to change your SQL. You have some options:

1. Use the IN(), to match right. Thus, returns movies where category is EQUAL "action", but not "animation" or "action-terror".

   SELECT * FROM filme WHERE categoria IN ( 'acao' )

2. Use the LIKE, but with less %. Thus, returns movies where category has "action" in front and not as word ending.

   SELECT * FROM filme WHERE categoria LIKE 'acao%'

Here is a Sqlfiddle, showing an example of both cases.

Answer 2

Assuming the content of $_GET['category'] be it for example:

$_GET['category'] = "ação, comédia, romance";

You could do it like this:

$categorias = !empty($_GET['category']) ? $_GET['category'] : '';

$categorias = implode("', '", explode(', ', $categorias));

$sql  = "SELECT * 
         FROM filme 
         WHERE 
            categoria IS NOT NULL AND 
            categoria IN ('{$categorias}') 
         ORDER BY nome LIMIT 25";

The excerpt $categorias = implode("', '", explode(', ', $categorias)); is doing the following:

The part explode(', ', $categorias) explodes the contents of the variable that is "ação, comédia, romance" in a array ["ação", "comédia", "romance"]. So the part implode("', '", explode(', ', $categorias)) "glue" that content that was "blown up" into a string using as a separator for each part to string ', ' then the result now is "ação', 'comédia', 'romance".

With this result we can use on condition IN SQL to say that the field has to have a value similar to any of the elements ie categoria IN ('ação', 'comédia', 'romance') which means the value of the field categoria must be equal to ação or comédia or romance.

Or you can even concatenate several LIKEs, as I was doing:

$categorias = explode(', ', $categorias);

$sql  = "SELECT * 
         FROM filme 
         WHERE 
            categoria IS NOT NULL AND (";

foreach ($categorias as $categoria) {
    $sql .= " categoria LIKE '%{$categoria}%' OR ";
}

$sql = rtrim($sql, ' OR ') . ") ORDER BY nome LIMIT 25";

Answer 3

I suggest you edit your question, as you mentioned in an answer, your problem is another.

My answer to the problem:

Use this function, call clean, to remove the spaces or traces between the characters:

function clean($string) {
   $string = str_replace(' ', '', $string); // Substitui os espaços vazios.
   $string = str_replace('-', '', $string); // Substitui os traços 

   return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Remove todos os special chars.
}

echo clean('anim a c a o');
echo '<br />';
echo clean('anim-a-c-a-o');

Exit:

animacao
animacao

ONLINE EXAMPLE