Relative Scores broken

[Wyliemaster]

Error

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', stars FROM (\n\t\t\t\t\t\t\tSELECT @rownum := @rownum + 1 AS rank, stars, extID, isBan' at line 1 in [SERVER_PATH]/incl/scores/getGJScores.php(83)

Error logs say the issue is around here

Code Snippet

	$f = "SELECT rank, stars FROM (
		SELECT @rownum := @rownum + 1 AS rank, stars, extID, isBanned
		FROM users WHERE isBanned = '0' AND gameVersion $sign ORDER BY stars DESC
		) as result WHERE extID=:extid";
	$query = $db->prepare($f);
	$query->execute([':extid' => $extid]);

After some debugging it appears to be @rownum which is causing the issue

Server version: 8.0.20-0ubuntu0.20.04.1 - (Ubuntu)

[Wyliemaster]

I've come up with a solution however a few things to note:

• My server is heavily modified so this may not even be a problem that needs to be solved
• This code replaces Lines 72-88

code

if($type == "relative"){
	$user = $result[0];
	$extid = $user["extID"];

	// TL;DR fetches all users who aren't banned and have more stars than the account and then returns the count as rank
	$query = $db->prepare("SELECT `rank`
	FROM (
		SELECT
			next_user.stars,
			next_user.extID,
			COUNT(*) AS `rank`
		FROM
			users AS next_user
		JOIN
			users AS prev_user
			ON next_user.stars < prev_user.stars
			OR (next_user.stars = prev_user.stars AND next_user.extID = prev_user.extID)
		WHERE
			next_user.isBanned = '0'
			AND next_user.gameVersion $sign
		GROUP BY
			next_user.stars, next_user.extID
	) AS ranked_users
	WHERE
		extID = ?"); // don't need :extid since it isn't used multiple times
	$query->execute([$extid]);

	$data = $query->fetch();
	$xi = $data["rank"] - 1;
}