Items 'like' this one

I'm looking for some general advice on how to go about finding items 'like' the current one.

In my current example I have three tables like so (omitting unrelated data):

games
-game_id

genres
-genre_id

genres_data
-game_id
-genre_id

How can I go about findin开发者_JAVA技巧g games that have genres in common with the current one, from the ones that have all the same genres, descending to ones that only have one in common with it (limited of course to a few rows) with a given row from games?

What's the preferred method of finding like items?

---

Try this:

 SELECT game_id, COUNT(genre_id) AS genres_in_common
 FROM genres_data
 WHERE 
    genre_id IN 
       (
        SELECT genre_id
        FROM genres_data 
        WHERE game_id = <game you're searching with>
       )
    AND
       game_id != <game you're searching with>
 GROUP BY game_id
 ORDER BY genres_in_common DESC
 ;

The subquery grabs a list of all genre_ids associated with your game, and the main query uses that to search genres_data for any record that matches one of them. In other words, it searches for any game which is associated with any genre your "search game" is associated with.

Because a game can have multiple genres, this query would return the same game_id multiple times, and if you also reported the genre_id on these records they would each show a different genre_id. What we do to find the ones with the most in common is to group the results by each game_id, and we add the COUNT(genre_id) in the main SELECT to show how many different genre_ids there were for each game_id returned in that query.

From there, it's a simple matter of ordering that count of common genres in descending order, so that the games with the most genres in common will be listed first.

I also added a second criterion to the main query to exclude the game you're searching on from the results, otherwise that game would always have the most matches, for obvious reasons.

Hope that helps.

---

Surely the way to do this for just a single game is to first grab its genres and then loop over them to create a new query:

$query = "SELECT `genre_id` FROM `genres_data` WHERE `game_id` = 'your_game_id_here';"
$genre_id_result = mysql_result($query, $dbconn);
$num = mysql_num_rows($genre_id_result);

if ($num > 0) {

    $query = "SELECT `game_id` FROM `games` WHERE ";

    for ($i=0;$i<$num;$i++) {

        $genre_id = mysql_result($genre_id_result, $i, "genre_id");

        if ($WhereSQL == "") {
                $WhereSQL = "genre_id = '$genre_id' "
            } else {
                $WhereSQL .= "AND genre_id = '$genre_id' "
            }
        }

        $GamesInCommonResult = mysql_result($query . $WhereSQL, $dbconn);

  }

You could set up a loop to do this for every game in the database and then collate your results. I can't think of how to do this in a single query at the moment.

I'm also a little unsure on your question as either you're looking for the genres that are the most popular (as games with these genres will likely be returned as having the most other games with the same genre in common) or you are looking individually for other game_ids of games in common with another game which might be more useful.