Give Same Rank Where Points Are The Same

[johnnyd1963]

Hi it's my first time here.

I hope some one wil help me with the following

 

I have a point with rank system

 

its olmost finished

 

i have only one problem. How can i give the same rank where points are the same.

 

i have now the following code

 

$jaarnu = date('Y', strtotime('now'));

$query = "SELECT * FROM narr_turnier_".$onlyconsonants." WHERE turnier_jaar =".$jaarnu." ORDER BY punkten desc";
$result=mysql_query($query);
$num=mysql_num_rows($result);

$i=0;

while ($i < $num) {
$id=mysql_result($result,$i,"id");
$query1="UPDATE narr_turnier_".$onlyconsonants." SET rang='".($i + 1 )."' WHERE id =".$id." && turnier_jaar =".$jaarnu." ";
mysql_query($query1) or die (mysql_error());

$i++;

} 

 

a little axplanation: rang = Rank and punkten = points

 

I want it as follow:

points rank

150 1

150 1

149 2

147 3

146 4

 

and when the input is again 149 the rank must be again 2

 

At the moment i have the ranks 1 2 3 4 5 and so on and that's not the right thing.

 

thanks in advice

 

john

Edited October 19, 2012 by johnnyd1963

[Andy123]

An SQL query within a loop always makes me nervous, but optimization is besides the point. Also, I would personally suggest using "AND" with MySQL, even though I know that && is perfectly fine. To me it is just more normal in SQL and more standard for other vendors.

 

Anyways, to get to your actual question; I am quite confused about your domain, so I have just written a possible solution below, but you will probably have to adapt it to your specific domain that you know much better than I do. I do, however, not doubt that there is a better way to go about it, even with a single SQL query.

 

$i = 0;
$points = 0;
$rank = 1;
$lastPoints = null;

while ($i < $num) {
$id = mysql_result($result, $i, "id");
$points = mysql_result($result, $i, "punkten");

if ($points !== $lastPoints) {
$rank = ($id + 1);
}

$query1 = "UPDATE narr_turnier_" . $onlyconsonants . " SET rang = " . $rank . " WHERE id = " . $id . " AND punkten = $points AND turnier_jaar = " . $jaarnu;
mysql_query($query1) or die (mysql_error());

$lastPoints = $points;

$i++;
}

 

Above, I have added a conditional statement to your while loop. The rank should only be increased if the points is not the same as the last one. However, I did not find any reference to the points in your code, so I just made a guess. Without understanding your domain better, that's all I can do for you right now. I hope I didn't make any silly mistake in the code, by the way, as it was written pretty fast.

Edited October 19, 2012 by Andy123

[Barand]

Someone posted a query that did exactly what you are trying to do. It was quite recent so I'll try and find the link for you

[Barand]

here: http://forums.phpfreaks.com/topic/269605-php-mysql-displaying-ranks-over-multiple-pages/

[johnnyd1963]

oke it's not workin because the ranks wil give 321 321 and so on

 

if you want to see lokk at

 

http://www.narrengilde-kohlscheid.de/php/login/testwolf/punktenliste_tanzturnier_zufugen.php

 

first choose Starter Bambini Garde zufugen then click gewahlt

[johnnyd1963]

i come back tomorrow i got it allmost.

 

$i=1;
$j=0;
$con=mysql_connect("localhost","ngkdbuser","Kohlscheid11");
if(!$con)
{
die("Couldn't connect to the database");
}
mysql_select_db("narrengilde",$con);
$sql=mysql_query("SELECT * FROM narr_turnier_".$onlyconsonants." WHERE turnier_jaar =".$jaarnu." ORDER BY punkten desc") ;
while($row=mysql_fetch_assoc($sql))
{
$id=$row['id'];
$total=$row['punkten'];
$sqlx="SELECT * FROM narr_turnier_".$onlyconsonants." where punkten='$total'";
$rowcount=mysql_num_rows(mysql_query($sqlx));
if($rowcount==1)
{
$sql22=mysql_query("update narr_turnier_".$onlyconsonants." set rang='$i' where id='$id' AND turnier_jaar = " . $jaarnu."");
mysql_query($sql22,$con);
$i=$i+1;
}
if($rowcount>1)
{
$j=$j+1;
if($j==$rowcount)
{
$sql22=mysql_query("update narr_turnier_".$onlyconsonants." set rang='".($i)."' where id='$id' AND turnier_jaar = " . $jaarnu."");
mysql_query($sql22,$con);
$i=$i+$rowcount;
$j=0;
}
else
{
$sql22=mysql_query("update narr_turnier_".$onlyconsonants." set rang='".($i)."' where id='$id' AND turnier_jaar = " . $jaarnu."");
mysql_query($sql22,$con);
}
}

}

[Barand]

Example

mysql> SELECT pid,
   -> @rownum := @rownum + 1 AS row,
   -> @rank := if (@prev<>points, @rownum, @rank) AS ranking,
   -> @prev:= points AS points
   -> FROM bg_points
   -> INNER JOIN (
   ->     SELECT @rownum := 0, @prev := -1, @rank := 0
   ->     ) as x
   -> ORDER BY points DESC
   -> ;
+-----+------+---------+--------+
| pid | row  | ranking | points |
+-----+------+---------+--------+
|   4 |    1 |       1 | 19     |
|   7 |    2 |       2 | 18     |
|   8 |    3 |       2 | 18     |
|  24 |    4 |       2 | 18     |
|   1 |    5 |       5 | 17     |
|  22 |    6 |       5 | 17     |
|  26 |    7 |       5 | 17     |
|   5 |    8 |       8 | 16     |
|  17 |    9 |       8 | 16     |
|  25 |   10 |       8 | 16     |
|  18 |   11 |      11 | 15     |
|  23 |   12 |      11 | 15     |
|   2 |   13 |      13 | 14     |
|  19 |   14 |      14 | 13     |
|  21 |   15 |      15 | 12     |
|   6 |   16 |      16 | 11     |
|   3 |   17 |      17 | 10     |
|  20 |   18 |      17 | 10     |
+-----+------+---------+--------+

[JohnTipperton]

what about using rank() function in a query.

 

or try to use my sample query

 

SELECT    first_name,
	  age,
	  gender,
	  @curRank := @curRank + 1 AS rank
FROM	  person p, (SELECT @curRank := 0) r
ORDER BY  age;

[Barand]

Oooh, what a novel idea John - using a query!.

 

But you don't seem to have grasped the ranking problem from the original post. All yours does is a row count, not a ranking.

[Pikachu2000]

Looks like it was probably a cut and paste from here: http://stackoverflow.com/questions/3333665/mysql-rank-function

[johnnyd1963]

I got the solution :happy-04:

 

Here it is

 

$sql=mysql_query("SELECT * FROM narr_turnier_".$onlyconsonants." WHERE turnier_jaar =".$jaarnu." GROUP BY punkten desc") ;
$total_rows=mysql_fetch_assoc($sql);
$num_rows = mysql_num_rows($sql);

if($total_rows>1)
{

$i=0;
while ($i < $num_rows) {$id=mysql_result($sql,$i,"punkten");
$query1="UPDATE narr_turnier_".$onlyconsonants." SET rang ='".($i + 1)."' WHERE punkten =".$id." && turnier_jaar =".$jaarnu." ";
mysql_query($query1) or die (mysql_error());

$i++;

}
}

 

if you know it it's simpel just think logical

 

thanks for the help to all

Edited October 21, 2012 by johnnyd1963