Page 1 of 1
Recommender System
Posted: Wed Jun 30, 2010 2:55 am
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>";
}
}
?>
Re: Recommender System
Posted: Wed Jun 30, 2010 7:53 am
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.
Re: Recommender System
Posted: Wed Jun 30, 2010 11:03 am
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?
Re: Recommender System
Posted: Wed Jun 30, 2010 11:15 am
by rnoack
try doing SELECT DISTINCT ?
Re: Recommender System
Posted: Thu Jul 01, 2010 10:55 am
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>";
}
?>
Re: Recommender System
Posted: Thu Jul 01, 2010 11:39 am
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.
Re: Recommender System
Posted: Thu Jul 01, 2010 11:47 am
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>";
}
?>
Re: Recommender System
Posted: Thu Jul 01, 2010 9:01 pm
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>";
}
?>
Re: Recommender System
Posted: Sat Jul 03, 2010 3:08 pm
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!
Re: Recommender System
Posted: Sat Jul 03, 2010 5:18 pm
by JakeJ
Just use != [current movie] in your where clause.