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!
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.
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?
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?
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.
<?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>";
}
?>