guys, I’ve crashed into a roadblock while am developing my website, and I really hope you help me this is cause I don’t know what to do.
I have a gaming website that is dynamic get the data from the database and show it on the page, I have 3 tables in the database one for the games named browsergames and one for reviews namedgames_reviews and one for rating named rating and am using this code to select from these tables.
// pagination
// get the page name from the URL
if (isset($_GET['page'])) {
// set the page name from the URL to the page var
$page = $_GET['page'];
}else{
/* if the word page isn't on the URL that means the use in the main page or the first page */
$page = 1;
}
// if the var $page = 1 or empty that mean the user still in the home page(index)
if ($page == '' || $page == 1) {
$page1 = 0;
}else{
$page1 = ($page*16)-16;
}
// get the file name from the URL and remove the file exertion if exists
$fileName = strtolower(basename($_SERVER['PHP_SELF'], '.php'));
// see if the url refer to the index page
if ($fileName == "index") {
$fileName = 'index';
// order the data from the database DESC
$orderBy = "ORDER BY uploadDate DESC";
}
// see if the url refer to the top games page
if($fileName == "top-games"){
$fileName = "index";
$orderBy = "ORDER BY games_reviews.rating DESC";
}
// see if the url refer to the most viewed page
if($fileName == "most-viewed"){
$fileName = "index";
$orderBy = "ORDER BY games_reviews.seenCount DESC";
}
// get the games from the browsergames table and show the ratings of the games from the rating table and the the review game link from the games_reviews table and show them in the pages
$getGame = "SELECT browsergames.id,browsergames.game_name,browsergames.game_link,browsergames.game_img_path,browsergames.game_tag,browsergames.game_info,browsergames.reviewGameName
,browsergames.uploadDate,browsergames.videoOverview,rating.game_id,rating.rating
,games_reviews.gameId,games_reviews.seenCount FROM browsergames LEFT JOIN rating ON
browsergames.id = rating.game_id LEFT JOIN games_reviews ON
browsergames.id = games_reviews.gameId WHERE game_tag LIKE '%".$fileName."%'
".$orderBy." LIMIT ".$page1.", 16";
this is just the code that select data, now let get to the problem browsergames table
I have 2 games in this table and one of the games has been rated 3 times and the other been rated 1 in the rated table, now the game that been rated three times get repeated in the page three time i don’t know why what wwnt is to show games based in the rating in the top games based on which game got the must rateing first and DESC getting the avg of the rating to each game and show the game only once with the avg rating but this is something wrong with my SQL query. please some help. i can post the whole code if this didn’t help you to fix the problem the code is too big.