Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts

    I want to count database results and list them

    I am using this code to count and sort from my database:

    PHP Code:
    $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:
    PHP Code:
    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,
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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:
    PHP Code:
    $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); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •