CODEWITHSUNDEEP
phpmysqlsqlwhere-clause
Ataman
Ataman

Reputation: 2590

SQL Where statement that matches words with different order

I have the following mysql code:

SELECT * FROM top_movies WHERE genre LIKE '%$genre%'

$genre is a string such as 'Comedy Drama'

and genre column in the database has strings such as Comedy Drama.

If $genre is Comedy Drama it will match to Comedy Drama. But also i want it to match to Drama Comedy.

How can I achieve this?

Thanks.

NOTE: $genre is a dynamic variable, i dont know how many words it contain, in what order or which words it has.

NOTE2: I am using PHP so any operations with PHP is welcomed.

Upvotes: 0

Views: 151

Answers (1)

Ionut Hulub
Ionut Hulub

Reputation: 6326

in php you should split the genres into words

$genre = explode($genre," ")

and $genre becomes and array, each element containing one word

ad you should do

$query = ""
foreach $genre as $element
{
  if (strlen($query) > 1)
  {
    $query += "AND "
  }
  $query += "genre LIKE  %"+$element + "% "
}
$query = "SELECT * FROM top_movies WHERE " + $query

this way it searches for each individual element in genre no matter the order

Upvotes: 3

Related Questions