...

View Full Version : I want to count database results and list them



countrydj
07-19-2012, 05:22 PM
I am using this code to count and sort from my database:


$qry = "SELECT entertainer, count(*) FROM final_results GROUP by entertainer ORDER by count(*) DESC LIMIT 5"; This is the code that I use to display the results:

if(!($results = mysql_query($qry, $link))){
displayErrMsg(sprintf("Error in executing %s query", $qry));
displayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
exit();
}

$numresults = mysql_num_rows($results);
if($numresults > 0){

while($row = mysql_fetch_object($results)){
$cat = $row->entertainer;

if ($cat != "") {
$main_content .= '<div align=left><font size=2><b>'.$cat.'<b></font></div>';
}
else {
$main_content .= '<font size=2><b>No votes</b></font>';
}
I get the right result, in as much as it lists all the contents in order of popularity, i.e.


Entertainer of the year award UK:

Henry Smith
Gary Perkins
John Permentor
Darren Busby
Richard Palmer


What I would like is the top 5 results to display, with a count for each of them and then a total count of all the results.

e.g.



Entertainer of the year award UK:

Henry Smith (110)
Gary Perkins (83)
John Permentor (66)
Darren Busby (21)
Richard Palmer (15)

Total votes = 366

I would appreciate any help and advice.

Thanks,

Fou-Lu
07-19-2012, 06:16 PM
Since you're dealing with a fetch_object, the easiest solution is to simply alias that count(*) to something like 'rcdcnt', then you can fetch it off of the $row->rcdcnt.
For the end total, just add a variable like $i, and increment it:


$iTotal = 0;
while($row = mysql_fetch_object($results)){
$cat = $row->entertainer;
$cnt = $row->rcdcnt;
$iTotal += $cnt;
if ($cat != "") {
$main_content .= printf('<div align="left"><font size="2"><b>%s (%d)</b></div>' . PHP_EOL, $cat, $cnt); // May want to switch to using CSS instead of these old deprecated properties and elements
}
else {
$main_content .= '<font size=2><b>No votes</b></font>';
}
}
printf('Total votes = %d' . PHP_EOL, $iTotal);



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum