Use explodes to separate categories

Asked

Viewed 859 times

0

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

3 answers

3


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.

  • If I understand your question correctly, you are wanting to take an array of categories and turn into several SQL likes?

  • face your answer is with the same problem as his and mine she continues attributing animation in action because of the anim-a-a-a-o that is the mistake I want to solve and I can’t

  • I elaborated a little more - unfortunately in your question you did not mention that this was your problem.

  • not intendi right where I should use the IN in my code I used as you said Where category in ('$category') ai it gave syntax error

  • The mistake is anim-a-c-a-o or anim a c a o??

  • How so anim-a-c-a-o? You’re saying you run this through the URL: ?category=anim-a-c-a-o? I don’t get it...

Show 1 more comment

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";
  • in your code in the first missing part you put it ? between the 2 get was giving error syntax tidied up but it was not he this lynching only the first yet I will upload 2 photos for you to see

  • here are the 3 images in the database showing category this as action and animation but it does not pull for animation: http://imgur.com/1qzfafh,7CuYOJm,dv1EoMo#0 http://imgur.com/1qzfafh,7CuYOJm,dv1EoMo#1 http://imgur.com/1qzfafh,7CuYOJm,dv1EMo#2

  • along with the second excerpt of code that Oce showed up to be sure but he is linking animation in action without me telling or is he picking up action of animation and assigning action

  • Are you placing multiple categories in the same table field? If it is you need to normalize your bank, create a table just to relate the movies and categories (id_filme, id_categoria) @Leonardocosta

2

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

Browser other questions tagged

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