[RESOLVED] possible with query? or must use PHP organize data

[Mgccl]

	

function get_rank($id){
                $query = "SELECT user_id ".
		"FROM user_prof ".
		"ORDER BY exp DESC";
                $result = mysql_query($query) or die (mysql_error());
	$row = mysql_fetch_array($result) or die (mysql_error());
	//loop to get rank of the user
	$i = 0;

$num_row = mysql_num_rows($result);

while ($row['user_id'] != $id) {
	if($i >= $num_row){
		break;
	}
	$i++;
}
return $i;
}

see what I did is
1. get all data in the database
2. use a while loop in PHP find the place of an id(because all the data are fetched and ordered)

so I used PHP to find the place of the id
but can MySQL do it?
like return only where the id will locate if order in somekind of way?

[nnichols]

Either of these should work, though I'm not 100% sure about the subquery syntax -

function get_rank($id) {
	//find out value of exp for user
	$sql = "SELECT exp FROM user_prof WHERE user_id = $id";
	$result = mysql_query($query) or die (mysql_error());
	$row = mysql_fetch_array($result) or die (mysql_error());
	$exp = $row['exp'];

//count all users with a higher exp
$sql = "SELECT COUNT(*)+1 AS rank FROM user_prof WHERE exp > $exp";
$result = mysql_query($query) or die (mysql_error());
$row = mysql_fetch_array($result) or die (mysql_error());
$rank = $row['rank'];

return $rank;
}

/* OR USING SUBSELECT (MYSQL >= 4.1) */

function get_rank($id) {
	$sql = "SELECT COUNT(*)+1 AS rank FROM prof_user WHERE exp > (SELECT exp FROM prof_user WHERE user_id = $id)";
	$result = mysql_query($query) or die (mysql_error());
	$row = mysql_fetch_array($result) or die (mysql_error());
	$rank = $row['rank'];

return $rank;
}

Give them a try and see how you get on 😉

[Mgccl]

great... this is just what I wanted... wow... looks like subquery is very useful. going to learn about it