Hi. Long time lurker, first time poster here.
I am building a topsites list, and in the process of ranking sites have stumbled on a problem. I list sites in order of the sum of their views in the last 7 days, from topsite_views which contains the number of views for any given day there is more than 1 view. I know how to do this.
My trouble is how to assign an actual rank number to the sites. I need to find how many sites are above them and add 1 (I know this much), but I don't know how to compare them when the actual calculation takes place inside the query.
Here is an example:
First, I find out the sum of views in last 7 days for the site in question ($valuex), then I want to see how many sites are better:
$qry = "select sum(views) as viewsum from topsite_views where viewsum >= '$valuex'";
Obviously, the query fails because it doesnt know what viewsum is until the query is finished... yada yada..
HAve you any suggestions on how to do this? Many thanks in advance!
Counting results with sum greater than a value
Moderator: General Moderators
Well.. I suppose you could always
Code: Select all
$i = "1";
$rows = mysql_num_rows($query) or die(mysql_error();
while ($i <= $rows) {
echo "#".$i." - blah<br />";
$i++;
}Code: Select all
<?php
$number_to_show=7;
$result = mysql_query(
sprintf(
"
SELECT
SQL_CALC_FOUND_ROWS
*
FROM
`sites`
ORDER BY
`whatever` ASC
LIMIT
%s
",
$number_to_show
)
);
$result_count=mysql_query('SELECT FOUND_ROWS();');
$count = (int)mysql_result($result_count,0,0);
mysql_free_result($result_count);
$i=0;
while($row=mysql_fetch_assoc($result)) {
//print_r($row);
echo 'There are '.($count-$i).' entries after this one..';
$i++;
}
mysql_free_result($result);
?>