Recommender System

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Recommender System

Post by tito85 »

Hi,

I am trying to make a simple query in a movie website to recommend movies similar to the one the user is viewing.

The problem I am having is that the result of the reccommender is sometimes outputting a recommended movie more then ones in the same result.

I don't know if it is a query problem or not. Maybe someone can help.

Any help would be very Helpful. Thanks!

Code: Select all

<?php
                  echo "<div class=\"blog2\"><h4 style=\"margin: 0\">We Recommend this Movie for you:</h4><p />";
                  echo "<hr style=\"border: 1px solid #06C\" />";
                 $query = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE genretypes.GenreTypeID LIKE '%" . $movies2['GenreID'] . "%' AND Rating != 0 ORDER BY RAND() LIMIT 0,7";
               $recommendations = mysql_query($query);
                           if (!$recommendations) {
    echo mysql_error();
}
                  if (mysql_num_rows($recommendations) > 0) {
                      echo "<table align=\"center\"><tr>";
                      while ($recommendation = mysql_fetch_array($recommendations)) {
                          echo "<td><a href=\"moviedetails.php?id=" . $recommendation['MovieID'] . "\">" . '<img src="Images/Movies/' . $recommendation['Filename'] . '" width="125" height="185" />' . "</a></td>";
                      }
                      echo "</tr></table>";
                  }
                  echo "</div>";
              }
?>
<?php
              } else
              {
                  echo "<center><b>The movie you are trying to access does not exist</b></center>";
              }
          }
?>
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Recommender System

Post by Jade »

Try running your query in mysql and looking at your result set. You're doing 2 inner joins... you probably have duplicate data in one of your tables that's causing the duplication in the query, or perhaps you need to be joining on another field and it's missing from your query resulting in the duplicates.
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Recommender System

Post by tito85 »

What I have is that since the recommander is based on the viewed movie and the recommender query is based on the current movie genre, if the current movie has more than one genre it is outputting same movies that have the same genre therefore outputting them more then one. Is there anything I can do to stop this from happening?
rnoack
Forum Commoner
Posts: 34
Joined: Mon May 03, 2010 12:38 am

Re: Recommender System

Post by rnoack »

try doing SELECT DISTINCT ?
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Recommender System

Post by tito85 »

I used a GROUP BY movies.MovieID and it seems to be working.

However I thing that the "LIKE '%" . $movies2['GenreID'] . "%'" is not working properly. Because when I run the query in MySQL and I insert manually a GenreID it is working fine. But when running this on the website a lot of results are coming out. I don't know why...

Any help please maybe I can use something else instead of LIKE..... in my query?

Thanks!

Code: Select all

<?php
                  echo "<div class=\"blog2\"><h4 style=\"margin: 0\">We Recommend this Movie for you:</h4><p />";
                  echo "<hr style=\"border: 1px solid #06C\" />";
                 $query = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE genretypes.GenreTypeID LIKE '%" . $movies2['GenreID'] . "%' AND Rating != 0 ORDER BY RAND() LIMIT 0,7";
               $recommendations = mysql_query($query);
                           if (!$recommendations) {
    echo mysql_error();
}
                  if (mysql_num_rows($recommendations) > 0) {
                      echo "<table align=\"center\"><tr>";
                      while ($recommendation = mysql_fetch_array($recommendations)) {
                          echo "<td><a href=\"moviedetails.php?id=" . $recommendation['MovieID'] . "\">" . '<img src="Images/Movies/' . $recommendation['Filename'] . '" width="125" height="185" />' . "</a></td>";
                      }
                      echo "</tr></table>";
                  }
                  echo "</div>";
              }
?>
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Recommender System

Post by Jade »

Try printing out your SQL statements to make sure you're getting values that you expect to be in the Genre field.

Remember that the LIKE with wildcards (%) will search for anything that contains the string.

Let's say you have the following genres:

[text]Child - Family - Fun
Children - Animated
Young - Child - Educational
Young - Adult - Child[/text]

If you search for genre's like '%child%' what results will you get? All of them. This will look for placement of the word child anywhere in the string.

If you search for genre's like 'child%' you'll only all of them as well. But if you searched for genre's like 'children%' or like '%children%' you'll only get the first result.

If you search for genre's like '%child' you'll only get the last genre that ends with the word child.
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Recommender System

Post by tito85 »

I am trying this now...

But it seems like I am having a problem with this part "$genretype = $result4['GenreTypeID'];"

Code: Select all

<?php
                  //Recommendation Section
				  $select4 = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE movies.MovieID = '" . $_GET['id'] . "' ORDER BY movies.MovieID";
              $result4 = mysql_query($select4);
              $genretype = $result4['GenreTypeID'];
			      echo "<div class=\"blog2\"><h4 style=\"margin: 0\">We Recommend this Movie for you:</h4><p />";
                  echo "<hr style=\"border: 1px solid #06C\" />";
                 $query = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE genretypes.GenreTypeID = " . $genretype . " AND Rating != 0 GROUP BY movies.MovieID ORDER BY RAND() LIMIT 0,7";
               $recommendations = mysql_query($query);
			   if (!$recommendations) {
    echo mysql_error();
}
                  if (mysql_num_rows($recommendations) > 0) {
                      echo "<table align=\"center\"><tr>";
                      while ($recommendation = mysql_fetch_array($recommendations)) {
					  echo $recommendation['MovieID'];
                          echo "<td><a href=\"moviedetails.php?id=" . $recommendation['MovieID'] . "\">" . '<img src="Images/Movies/' . $recommendation['Filename'] . '" width="125" height="185" />' . "</a></td>";
                      }
                      echo "</tr></table>";
                  }
                  echo "</div>";
              }
?>
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Recommender System

Post by Jade »

You need to do this instead:

Code: Select all

<?php
                  //Recommendation Section
                                  $select4 = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE movies.MovieID = '" . $_GET['id'] . "' ORDER BY movies.MovieID";
              $result4 = mysql_query($select4); //this returns a resource to the results

              $row = mysql_fetch_array($result4); //this generates an array with your results
              $genretype = $row['GenreTypeID']; //now you pull the data out of the array
              //print_r($row); //uncomment this line to see the full results returned by your query above

                              echo "<div class=\"blog2\"><h4 style=\"margin: 0\">We Recommend this Movie for you:</h4><p />";
                  echo "<hr style=\"border: 1px solid #06C\" />";
                 $query = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE genretypes.GenreTypeID = " . $genretype . " AND Rating != 0 GROUP BY movies.MovieID ORDER BY RAND() LIMIT 0,7";
               $recommendations = mysql_query($query);
                           if (!$recommendations) {
    echo mysql_error();
}
                  if (mysql_num_rows($recommendations) > 0) {
                      echo "<table align=\"center\"><tr>";
                      while ($recommendation = mysql_fetch_array($recommendations)) {
                                          echo $recommendation['MovieID'];
                          echo "<td><a href=\"moviedetails.php?id=" . $recommendation['MovieID'] . "\">" . '<img src="Images/Movies/' . $recommendation['Filename'] . '" width="125" height="185" />' . "</a></td>";
                      }
                      echo "</tr></table>";
                  }
                  echo "</div>";
              }
?>
 

tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Recommender System

Post by tito85 »

Hi,

Thanks it is working. Is it possible to not display the result that output the result of current vied movie?

Thanks for you help and time!
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Recommender System

Post by JakeJ »

Just use != [current movie] in your where clause.
Post Reply