Ranking Module using loops in PHP

Posted: June 5, 2012 in Programming
Tags: , , , , ,

Let us consider, we have  student records in a table and we have to calculate rank  and update table using php loops.

Database Name is student, table name is mid1 and fields are rollno, tot,rank

sample data

rollno  tot  rank

1         100     1

2        200      2

3        200      2

4        300      4

Note: Since i am using rank for my students i have to skip numbers if they got repeated. In the above example rollno 2 and 3 got same rank as 2 . Hence i skipped the rank 3 and moved to 4.
<?php
$i=1;
$j=0;
$con=mysql_connect(“localhost”,”username”,”password”);
if(!$con)
{
die(“Couldn’t connect to the database”);
}
mysql_select_db(“student”,$con);
$sql=mysql_query(“select * from mid1 order by tot DESC”) ;
while($row=mysql_fetch_assoc($sql))
{
$id=$row[‘rollno’];
$total=$row[‘tot’];
$sql3=”select * from mid1 where tot=’$total'”;
$rowcount=mysql_num_rows(mysql_query($sql3));
if($rowcount==1)
{
$sql2=mysql_query(“update mid1 set rank=’$i’ where rollno=’$id'”);
mysql_query($sql2,$con);
$i=$i+1;
}
if($rowcount>1)
{
$j=$j+1;
if($j==$rowcount)
{
$sql2=mysql_query(“update mid1 set rank=’$i’ where rollno=’$id'”);
mysql_query($sql2,$con);
$i=$i+$rowcount;
$j=0;
}
else
{
$sql2=mysql_query(“update mid1 set rank=’$i’ where rollno=’$id'”);
mysql_query($sql2,$con);
}
}

}

If u dont want to skip the ranks simply delete the variable $j and the line $i=$i+$rowcount.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s