Help fixing this code


#1

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.


#2

i think it will help if i didn’t use the left join and made tow diffrent select statement but i can’t think of a good way to do that


#3

But which rating do you want to actually show?


#4

i just want to show the average of the ratings in the rating table collect all the rating for the games and get the avg of it and order games based on the avg high ratings in the top the game that got most stars rating got in the top and the other below it


#6

You could probably usr MySQL’s AVG(COLUMN_NAME) to get the average rating


#7

i tryed it but the games in the page just disappear

$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,
	**AVG(rating.rating) AS 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";

#8

Probably do 2 queries instead of join or @Supun coulf probably come up with a better query?


#9
SELECT
	bg.id, bg.game_name, bg.game_link,
	bg.game_img_path,bg.game_tag,bg.game_info,bg.reviewGameName, 
	bg.uploadDate, bg.videoOverview,
	r.game_id, r.rating, 
	gr.gameId, gr.seenCount 
	FROM browsergames bg LEFT JOIN rating r ON 
    bg.id = r.game_id LEFT JOIN games_reviews gr ON 
    bg.id = gr.gameId WHERE game_tag LIKE '%".$fileName."%' 
    GROUP BY bg.id
    ".$orderBy." LIMIT ".$page1.", 16

Try this one. I added a GROUP BY statement.


#10

love love love, thanks mate it worked :heart_eyes::heart_eyes::heart_eyes::heart_eyes:


#11

I’m happy to see it working @AwadGorg :wink:


#12

how can i prevent SQL injection in the code above the select statement usingg prepare stmt cause when i try to use prepare stmt the page ■■■■■


#13
$query = "SELECT
	bg.id, bg.game_name, bg.game_link,
	bg.game_img_path,bg.game_tag,bg.game_info,bg.reviewGameName, 
	bg.uploadDate, bg.videoOverview,
	r.game_id, r.rating, 
	gr.gameId, gr.seenCount 
	FROM browsergames bg LEFT JOIN rating r ON 
    bg.id = r.game_id LEFT JOIN games_reviews gr ON 
    bg.id = gr.gameId WHERE game_tag LIKE ?
    GROUP BY bg.id 
    $orderBy 
    LIMIT ?,16"

$like = '%' . $filename . '%';
$limit = $page1;

$stmt = $mysqli -> prepare($query);

if (
    $stmt &&
    $stmt -> bind_param('si', $like, $limit) &&
    $stmt -> execute() &&
    $stmt -> store_result() &&
    $result = $stmt -> get_result() {
  
    // now $result is same as doing $mysqli -> query
   
   while ($row = $result -> fetch_assoc()) {
       echo $row['id'];
   }

}

Note that you cannot pass ORDER BY value as parameters. Therefore, make sure that $orderBy variable is something you create and not being created from user input.

// never do this
$orderBy = $_GET['orderBy']; 
// this is okay
if ($_GET['orderBy'] === 'name') {
    $orderBy = 'ORDER BY name';  
} else {
   $orderBy = 'ORDER BY time'; //default
}

#14

well thanks mate,
last think i hope heh.
let say if i want to run a php code after the user spend amount of time in the page like if the user didnt liv the page lets say 5 min sign him out or update his points in user lvl system how can i do so .