Ranking by hitsin and Hitsout coding help

phprock

Hello all,
Can anyone guide me with some code ...

I have table as follow

site | hitsin | hitsout
1 | 30 | 100
2 | 80 | 50
3 | 40 |40
4 | 30 |150

I want to rank by hitsin and if any site's hitsin has the same hitsin then rank by hitsout.


$result=mysql_query("select * from junk order by hitsin DESC",$link);

echo"<table width=100% border=1>";
echo"<tr>";
echo"<td><b>Site</b></td><td><b>hitsin</b></td><td><b>Hits out</b></td><td><b>Rank</b></td>";
echo"</tr>";
while($row=mysql_fetch_array($result)){
$r=mysql_query("select * from junk where hitsin>$row[hitsin]",$link);
$rowx=mysql_fetch_array($r);
$found=mysql_num_rows($r);
$rank=$found+1;
echo"<td>$row[site] </td><td>$row[hitsin]</td> <td>$row[hitsout]</td> <td>$rank</td>";
echo"<tr>";
}
echo"</table>";

Well, you can see that

site=1,Rank=3
site=2,Rank=1
site=3,Rank=2
site=4,Rank=3

or
Rank 1,site 2
Rank 2 site 3
Rank 3 site 4
Rank 4 site 1

As the site 1 and site 4 has the save hitsin, i want to rank them by hitsout.

so,site 1 rank should be as it's hitsout(100) is less then the site 4 hitsout.(150).

so,
Ranking position would be

site=1,Rank=4
site=2,Rank=1
site=3,Rank=2
site=4,Rank=3

or
Rank 1,site 2
Rank 2 site 3
Rank 3 site 4
Rank 4 site 1

Any help please

thanks in advance

LordShryku

order by hitsin DESC, hitsout DESC

phprock

wow that was too easy,
Thanks for the reply.

ok, Now i got my rank woriking,

Now i wan to add last rank..

Do you have any ideas how can i implement last rank.. any logic..

$link=db_connect();
$result=mysql_query("select * from junk order by hitsin DESC,hitsout DESC",$link);

echo"<table width=100% border=1>";
echo"<tr>";
echo"<td><b>Site</b></td><td><b>hitsin</b></td><td><b>Hits out</b></td><td><b>Rank</b></td><td><b>last Rank</b></td>";
echo"</tr>";
$i=0;
while($row=mysql_fetch_array($result)){
$r=mysql_query("select * from junk where hitsin>$row[hitsin]",$link);
$rowx=mysql_fetch_array($r);
$i++;
echo"<td>$row[site] </td><td>$row[hitsin]</td> <td>$row[hitsout]</td><td>$i</td><td>$i</td>";
// update the rank and record when rankd
$time=time();
@mysql_query("update junk set rank='$i',rankdate=$time where site='$row[site]'",$link);
echo"<tr>";
}
echo"</table>";

Thank you again for your help..

webdes03

To get the last one, you could add a time column to your database and use the MySQL NOW() function to automatically generate a 14 character timestamp... then just query your database by time descending limit 1, and you would get the last added row int he database.

There's probably other ways to do it, but thats one.

phprock

Thank you for the reply..

But thats not what i want..

I have rank and last rank.
Rank is current rank and last rank is /2 days ago rank.

And I want to display both rank and last rank.

Any concepts do that ..

webdes03

You use my method and LordShryku's method... you need to do two queries to get two different values.

Use the first query to load the current rank, and a second query to pull the old one.

phprock

Not sure..

Tables are

siteid,hitsin,hitsout,rank,lastrank

I want to show arrow up and down
and netural.

if the last rank was 1 and the now rank is 2 then i want to show down array.

if($row["lastrank"] !=0){
  if($row["lastrank"]>$row["rank"]) {
  $image="up.gif";
  }elseif($row["lastrank"]<$row["rank"]){
   $image="down.gif";
   }elseif($i=$row["lastrank"]){
     $image="nchange.gif";
     }else{
    $image="up.gif";
    }
} else {
  $image="up.gif";
  }

My question how i keep record of last record and when..

$resultx=@mysql_query("select * from topsites_user where status='0' ORDER BY hitsin DESC,hitsout DESC",$link) or die("Loading toplist failed".mysql_error());
$i=1;
while($rowx=@mysql_fetch_array($resultx)){
	mysql_query("update topsites_user set rank='$i', date='$date' where uid='$rowx[uid]'",$link);
	$time=time();
	$next=$time+60;
	/*
	if($time>$row["nextupdate"]){
	mysql_query("update topsites_user set lastrank='$row[rank]' where uid='$rowx[uid]'",$link);
	mysql_query("update topsites_system set nextupdate='$next', update='$time'",$link);
	}
	 */
$i++;
}

You can see that I m ranking each time page is load, $i,

But How do i keep the last record.

Let say at 12:00 AM

site 1 was top rank1 with hits 100
site4 was rank 2 with hits 70

NOw at 4:30 PM
Site1 goes to rank 2 with hits 120
site4 goes to rank 1 with hits 130

so i want to keep record
so site 1 last rank would be 1 with dwn arrrow and site 4 with last rank would be 2 with up arrow key.

Please help me

regards