creating a weekly top 10 with rank change and new entry

[MattG]

Hi,

I have a table of items which when clicked on the site by a visitor it adds 1 to the count in the database. I have a field for the overall 'clicks' and one that counts only the ones during the week which is reset to 0 at the end of the week while the overall is left alone.

What I want to do is automate what I am currently having to do manually which is create the top 10 myself to show what items have risen up the table and gone down and what are 'new entries' into the top 10.

Hopefully that makes sense. I was wondering if anyone could help me with an idea on how to code it so that I don't have to do it manually at the end of each week as I've got a mental block about how I can do it.

As an example...I want it to look something like Lycos' weekly top 50 as seen here http://50.lycos.com/ with the change column etc.

[kbc1]

To get the 'Top 10' just run a query against your table like such:

	$qry= "SELECT clickid, clickname, currentcount FROM clicks_table ORDER BY totalclicks DESC LIMIT 10";
	$res = mysql_query($qry);

Then just display the results as you like:

		while ($datarow = mysql_fetch_array($res)) {
			$clickid= $datarow['clickid'];
			$clickname = stripslashes($datarow['clickname']);
           $currentclicks = stripslashes($datarow['currentcount']);

		echo "<p>$clickname (Clicks to date:  $currentclicks)</p>";

	}
	mysql_free_result($res);

That should get you the Top 10 from your table.

[MattG]

kbc1 wrote:

To get the 'Top 10' just run a query against your table like such:

	$qry= "SELECT clickid, clickname, currentcount FROM clicks_table ORDER BY totalclicks DESC LIMIT 10";
	$res = mysql_query($qry);

Then just display the results as you like:

		while ($datarow = mysql_fetch_array($res)) {
			$clickid= $datarow['clickid'];
			$clickname = stripslashes($datarow['clickname']);
           $currentclicks = stripslashes($datarow['currentcount']);

		echo "<p>$clickname (Clicks to date:  $currentclicks)</p>";

	}
	mysql_free_result($res);

That should get you the Top 10 from your table.

What do you think would be the best way to do the rank change? I could put another field in to record the rank from the previous week and compare it to this week but with 1000's of entries in the table is there a better way to do it?

[kbc1]

Why not keep it simple and do something like showing what its position was last week which would all be done in the same query as above?

So

$qry= "SELECT clickid, clickname, currentcount, lastposition FROM clicks_table ORDER BY totalclicks DESC LIMIT 10";
    $res = mysql_query($qry); 

the 'lastposition' field would just be an integer which gets updated when the record moves position in your database.

So in your list of items you would have the following:

while ($datarow = mysql_fetch_array($res)) {
            $clickid= $datarow['clickid'];
            $clickname = stripslashes($datarow['clickname']);
           $currentclicks = $datarow['currentcount'];
           $lastposition = $datarow['lastposition'];

        echo "<p>$clickname (Clicks to date:  $currentclicks) | Last weeks Position ($lastposition)</p>";

    }
    mysql_free_result($res); 

Obviously your list would be styled much nicer and be in columns.