Member Avatar for zero_sequence

Hi to all,
i am building a site with movie reviews.. due to the
fact that a movie may have more than one category ex
action, adventure, sci fi etc i ended up using a sinlge
column to add all tha categories in comma delimited
form..

1st question.
is the structure wrong? do i have to alter the table
each time the user add a new category and store any
category as a individual column?

2nd question.

how can i find similar movies order by the most common
values?

ex

i have a action,fantasy movie

and then movies with

action,adventure,horror
adventure,fantasy
adventure,fantasy,action,horror
adventure,horror

i want to pick the first 3 with order 3,2,1 or 3,1,2

Edited by zero_sequence because: n/a
  • 2 Contributors
  • 9 Replies
  • 212 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

In a normalized database the tags would have their own (link-)table.

It depends on how you want to retrieve your data. It is possible to build a query in code that will return your result. Doing it in a single query would be very difficult. For a single category you can use FIND_IN_SET(). Checking more categories would require you to build a query consisting of more FIND_IN_SET()'s.

SELECT * FROM table WHERE FIND_IN_SET('action', categorycolumn)
Member Avatar for zero_sequence

Thank you pritaeas,

sorry for the misunderstanding.. the tags are in a different table...
in the actual review table there are only the serial of the checked categories ex 1,2,3,4 for action, adventure, horror, adult.. lets say that i use the find_in_set, then how can i order the results like the way i described above?

Member Avatar for pritaeas

You would have to create a query similar to this one:

SELECT * FROM table WHERE 
    FIND_IN_SET('action', categorycolumn) AND
    FIND_IN_SET('fantasy', categorycolumn)
UNION
  SELECT * FROM table WHERE 
    FIND_IN_SET('action', categorycolumn) AND
    NOT FIND_IN_SET('fantasy', categorycolumn)
UNION
  SELECT * FROM table WHERE 
    FIND_IN_SET('fantasy', categorycolumn) AND
    NOT FIND_IN_SET('action', categorycolumn)

You can imagine it gets worse when the number of tags increase.

Edited by pritaeas because: n/a
Member Avatar for zero_sequence

In case of creating a new table with movie id and categ id, how i can accomplish the same?

Member Avatar for pritaeas

I think you can create a query like this:

SELECT * FROM linktable WHERE category IN ('action', 'fantasy')
GROUP BY movie
ORDER BY COUNT(1) DESC,movie

The order by puts the movies with the most category hits on top.

Edited by pritaeas because: n/a
Member Avatar for zero_sequence

If there was a god of tetris you would definitely be god of mysql..
It works... could you expain how it works?

Member Avatar for pritaeas

The SELECT gets everything that has at least one of your specified categories. Then you group it by movie, so you can count how many records you found for that movie (the matching categories). The minimum will be 1, the maximum the number of categories in the IN clause. The order by puts the one with the most hits on top. If they are the same it orders them by title (in my imaginary table at least).

Thanks for the kudos, but I only know this because I've used it before. I'm more a grunt than a god.

Edited by pritaeas because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.