rank system

[johndohmen1963]

Who can help me with the following

i have 5 or more rows in a table the last column is called rank
one row is called points.

I want automatic insert the rank like
when a person has 123 points is rank 1
the second person with 120 point rank 2
the third person with 119 points rank 3

and when a forth or someone else person has the same point like 119 point even he must have rank 3

i have a peace of code but it does not work proper can someone help me

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

$count = 1;                     


$query12 = "SELECT * FROM narr_turnier_".$onlyconsonants." WHERE turnier_jaar=".$jaarnu." order by punkten desc";
            $result12 = mysql_query($query12) or die(mysql_error());

while($rows12=mysql_fetch_row($result12)){
 $updatequeryy = "UPDATE narr_turnier_".$onlyconsonants." SET rang=".$count."
 WHERE turnier_jaar=".$jaarnu."";   
        mysql_query($updatequeryy) or die (mysql_error());  


$count++;
        }

So as you see i wnt to update always.

Thanks in advice Jo

[pritaeas]

You need to remember the last rank assigned, and the number of points for the previous member. If the points are the same, assign the previous rank, otherwise assign the count. Try it first.

[johndohmen1963]

sorry couldn't responce earlier because i was sleeping

i changed in

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

$i=0;
while ($i < $num) {

$query1="UPDATE narr_turnier_".$onlyconsonants."  SET rang='$i' WHERE turnier_jaar =".$jaarnu."";
mysql_query($query);

echo $i;
++$i;
}   

it gives me nothing

if i echo $i i see the number 1 2 3 4 5 6 and so on until the last row

can you help me a little bit with code i can't fix it i am bussy for a whole day and tring everything

thanks in advice John

[pritaeas]

You will need at least to explain which table column is the person's unique ID.

Apart from that, mysql_numrows is incorrectly spelled.

[johndohmen1963]

oke i got it almost

i have and it works

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

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

++$i;
}   

there is only one thng How can i start the rank with number 1 and not 0(zerro)

[johndohmen1963]

oke i got it for the numbers

 SET rang='".($i+1)."' WHERE id =".$id+1."";

now when the someone has the same points i need the same rank

[diafol]

Perhaps I've got it wrong - but do you really need a stored rank? can this not be calculated dynamically when you run the query? But if you do, try not to run a mysql update query for each member.

$result = mysql_query("SELECT user_id, points,... FROM table WHERE `year` = '$year' ORDER BY points DESC");
$rank = 0;$award = -1; //this assumes points can never be negative - otherwise change it to 'xxx' or whatever you want.
while($data = mysql_fetch_assoc($result)){
    if($award != $data['points'])$rank++;
    $updateList[] = "($user_id, $rank)";
    $award = $data['points'];
    //check 
    //echo $data['user_id'] . ' | ' . $data['points'] . ' | ' . $rank . '<br />';
}
$updateString = implode(',',$updateList);
$update = mysql_query("INSERT INTO table (user_id,rank) VALUES $updateString ON DUPLICATE KEY UPDATE rank=VALUES(rank)");
...

I'm assuming this last SQL statement will work - not tested. I also assume that there is only one instance of each user in the table. The SQL should cause an update on every 'rank' - there should be no inserts at all. However, it does seem a bit contrived - anybody know of a better method?

[johndohmen1963]

yes there is only one instance of each user in a table in this year

and yes i need a stored rank.

[diafol]

SO did you try it?

[johndohmen1963]

yes but intil now i can't get it work.
The rank is always empty

[johndohmen1963]

i give you here the table

i made it in english

$sql1 ="CREATE TABLE IF NOT EXISTS narr_turnier_".$onlyconsonants." (
id int(11) NOT NULL auto_increment,
id_ competition_jear INT,
startnumber INT,
person_name VARCHAR(150) COLLATE utf8_unicode_ci,
club  VARCHAR(150) COLLATE utf8_unicode_ci,
points INT,
rang INT,
birthjear INT,
competition _jear INT,
PRIMARY KEY (id) )ENGINE=MyISAM DEFAULT CHARACTER SET utf8   
 COLLATE utf8_unicode_ci";
        mysql_query($sql1) or die(mysql_error());

if you want a link

to what i created goto http://www.narrengilde-kohlscheid.de/php/login/michel/punktenliste_tanzturnier_zufugen.php

select the radiobutton and click on gewahlt otherwise
$onlyconsonants dosn't work.

(tomorow i take the link away)

[johndohmen1963]

Can someone help me to create the code for the same rank where points are the same ?

[johndohmen1963]

oke have the solution found for someone who want it

$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++;

}
}

Thanks for the help to all