Not Returing?

[Joshua F]

I have released something to some friends of mine and some of them are having problems with what I released. The only problem is that one of my methods are not returning what should be returned.

 

The method is below.

	function getRank($playerName, $skill) {
	include 'system/config.php';
	if ($skill != -1) {
		$rank = 1;
		$query = $this->con->query("SELECT * FROM `hiscores` WHERE `playerRights` < ".$this->highestrights." ORDER BY `".$this->con->real_escape_string($skill)."` DESC") or die($this->con->error);
		while ($row = $query->fetch_assoc()) {
			if ($row['playerName'] == $playerName) {
				return $rank;
			}
			$rank++;
		}
	} else {
		$rank = 1;
		$query = $this->con->query("SELECT * FROM `hiscores` WHERE `playerRights` < ".$this->highestrights." ORDER BY `LVL` DESC, `XP` DESC");
		while ($row = $query->fetch_assoc()) {
			if ($row['playerName'] == $playerName) {
				return $rank;
			}
			$rank++;
		}
	}
}

 

Once again, the problem is that it won't return the value of $rank.

[Psycho]

My guess would be that  one of the queries is not returning any records. But, that logic is kinda inefficient. Why do you need to do this

				if ($row['playerName'] == $playerName) {
				return $rank;
			}

 

Just make the query so it only returns records where that condition is true. Go take a look here: http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by

[creata.physics]

Or you can change:

return $rank;

to

return $row;

 

Edit: nevermind, I didn't know you weren't trying to grab the data from the database, you're just trying to grab the proper integer that the rank was increased to.  So disregard my post.

[Joshua F]

My guess would be that  one of the queries is not returning any records. But, that logic is kinda inefficient. Why do you need to do this

				if ($row['playerName'] == $playerName) {
				return $rank;
			}

 

Just make the query so it only returns records where that condition is true. Go take a look here: http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by

That link blew my mind. I've tried it and came up with this query but it still doesn't work.

SELECT *
FROM (

SELECT @rownum := @rownum +1 AS rank
FROM `hiscores`
JOIN (

SELECT @rownum :=0
)r
ORDER BY 0 DESC

 

Also it did give an error,

#1248 - Every derived table must have its own alias

 

This is running it through PHPMyAdmin also.

[xyph]

You should provide us a dump of the tables you're dealing with, and the results you expect.

 

You can provide a raw export from PHPMyAdmin, or follow a similar format to that of the OP in the StackOverflow thread.

[Joshua F]

Table:

CREATE TABLE IF NOT EXISTS `hiscores` (
  `playerName` varchar(12) NOT NULL,
  `playerRights` int(1) NOT NULL DEFAULT '0',
  `isDonator` int(1) NOT NULL DEFAULT '0',
  `LVL` int(4) NOT NULL,
  `XP` bigint(10) NOT NULL,
  `0` int(9) NOT NULL DEFAULT '0',
  `1` int(9) NOT NULL DEFAULT '0',
  `2` int(9) NOT NULL DEFAULT '0',
  `3` int(9) NOT NULL DEFAULT '1155',
  `4` int(9) NOT NULL DEFAULT '0',
  `5` int(9) NOT NULL DEFAULT '0',
  `6` int(9) NOT NULL DEFAULT '0',
  `7` int(9) NOT NULL DEFAULT '0',
  `8` int(9) NOT NULL DEFAULT '0',
  `9` int(9) NOT NULL DEFAULT '0',
  `10` int(9) NOT NULL DEFAULT '0',
  `11` int(9) NOT NULL DEFAULT '0',
  `12` int(9) NOT NULL DEFAULT '0',
  `13` int(9) NOT NULL DEFAULT '0',
  `14` int(9) NOT NULL DEFAULT '0',
  `15` int(9) NOT NULL DEFAULT '0',
  `16` int(9) NOT NULL DEFAULT '0',
  `17` int(9) NOT NULL DEFAULT '0',
  `18` int(9) NOT NULL DEFAULT '0',
  `19` int(9) NOT NULL DEFAULT '0',
  `20` int(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (`playerName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

A few inserts(2):

INSERT INTO `hiscores` (`playerName`, `playerRights`, `isDonator`, `LVL`, `XP`, `0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`, `18`, `19`, `20`) VALUES
('bugatti', 0, 0, 768, 286403355, 49116430, 131264982, 25036430, 53400427, 144068, 19528000, 2089518, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 32800, 5790700, 0, 0),
('masta jay', 0, 0, 1154, 489791927, 24694390, 21525420, 164904390, 87618890, 13548508, 13058800, 15198329, 0, 13446640, 29400, 0, 112309950, 0, 0, 0, 0, 7760, 14100000, 9349450, 0, 0);

 

It's for a hiscores script(obviously) and it is supposed to get the "rank" that player has in that skill, each of the numbers in the table represents a skill, and the LVL and XP represent the total level and experience points. The numbers store the amount of experience points that player has in that skill, and I use a PHP function to get the level out of it.

 

P.S. I can supply a dump of inserts that have over 300 if needed.

[xyph]

Hmmm, I think we need to help you normalize your database a little bit more.

 

Can you provide me an example, or examples of what you'd want the SQL query to return? I think I know what you're looking for, but it would still help to give you a more accurate solution.

[Joshua F]

I'm not trying to make it return anything from the database, just the rank that use has in that specific "skill".

[xyph]

That's still data from the database This might help you out

 

SELECT
x.position,
x.playerName
FROM (SELECT
	t.playerName,
	@rownum := @rownum + 1 AS position
	FROM hiscores t
		JOIN (SELECT @rownum := 0) r
	ORDER BY t.2 DESC) x
WHERE x.playerName = 'masta jay'

 

Change t.2 to t.#, where # is the column you want to grab the rank for.

Change 'masta jay' to whichever user you want to grab the rank for.

 

mysql> SELECT
    ->  x.position,
    ->  x.playerName
    -> FROM (SELECT
    ->          t.playerName,
    ->          @rownum := @rownum + 1 AS position
    ->          FROM hiscores t
    ->                  JOIN (SELECT @rownum := 0) r
    ->          ORDER BY t.2 DESC) x
    ->  WHERE x.playerName = 'masta jay';
+----------+------------+
| position | playerName |
+----------+------------+
|        1 | masta jay  |
+----------+------------+
1 row in set (0.00 sec)

[Joshua F]

Cheers xyph, I need to learn some more SQL. Thanks again!