MySql order by COUNT and list the total count

[countrydj]

Many thanks for the tip about the $trings.
I get very confused what will work and what won't.
I have changed my script to make to slightly smaller.

I have tested again using count(*), and this is the result:

PHP Code:

$qry = "SELECT $field, COUNT(*) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT(*) DESC LIMIT 6";

and

$qry = "SELECT $field, COUNT(*) FROM $table order BY COUNT(*)"; 


gives:

Entertainer of the year award UK:
Henry Smith ()
Gary Perkins ()
John Permentor ()
Darren Busby ()
Richard Palmer ()

Total votes =

I now change it to:

PHP Code:

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT($field) DESC LIMIT 6";

and

$qry = "SELECT $field, COUNT($field) FROM $table order BY COUNT($field)"; 


gives:

Entertainer of the year award UK:
Henry Smith (6)
Gary Perkins (3)
John Permentor (2)
Darren Busby (2)
Richard Palmer (1)

Total votes = 14

If by chance there is some other reason, here is the full script for entertainer:

PHP Code:

$tr=1;

//######################### ENTERTAINER #######################
        $main_content .= '<td width=33% valign=top>';
$category = "Entertainer of the year award UK:";
$field = "entertainer";

        $main_content .= '<br><font size=2 color="#ffff00"><b>'.$category.'</b></font><p>';

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT($field) DESC LIMIT 6";
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);
echo $numresults;
if($numresults > 0){

// Print out ALL result
    while($row = mysql_fetch_array($results)){
$list = $row[''.$field.''];
    $main_content .= '<b><font size=2>'.$list.' ('.$row['COUNT('.$field.')'].')</b>';
    $main_content .= '<br />';

    }

$qry = "SELECT $field, COUNT($field) FROM $table order BY COUNT($field)"; 
     
$result = mysql_query($qry) or die(mysql_error());

// Print out TOTAL result
while($row = mysql_fetch_array($result)){
    $main_content .= '<br /><b><font size=2>Total votes = '.$row['COUNT('.$field.')'].'</b><p>';

    }
}

else {
        $main_content .= '<b><font size=2>No votes';
    }

        $main_content .= '</td>';

$tr++;
if($tr > 3){
        $main_content .= '</tr><tr>';
$tr=1;
} 


As I've been writing this reply, I remembered that I usd a similar code some time ago, using WHERE is NOT ???.
So I tried it in this script:

PHP Code:

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field != 'NULL' GROUP by $field ORDER by COUNT($field) DESC LIMIT 6"; 


I should have thought harder a few days ago and I might have figured this bit out.
However, I have never used COUNT before so I was really stuck without YOU !!

If you see any way of improving my script, I will be very happy to learn from you.

Very many THANKS....

[guelphdad]

The problem with COUNT(*) and COUNT($field) isn't the SQL it is the PHP.
That is because you aren't assigning a variable to COUNT(*) so you have no way of outputting that value as your PHP code is written.

Note too that this is incorrect: $field != 'NULL' the single quotes around NULL turn it into a string and not unknown or NULL. you also can't use != to test for NULL.

[Old Pedant]

Indeed. Why did you change from

SELECT $field, COUNT(*) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT(*) DESC LIMIT 6";

to

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field != 'NULL' GROUP by $field ORDER by COUNT($field) DESC LIMIT 6";

Those are not at all the same thing.

And you *can* get to COUNT() in PHP even though you didn't give it a name. You can get to it by its position in the fields. (That is, field #1.)

But it's much easier to use if you give it a name.

$sql = "SELECT $field, COUNT(*) AS theCount FROM $table " 
     . " WHERE $field IS NOT NULL "
     . " GROUP by $field "
     . " ORDER by theCount DESC LIMIT 6";

[countrydj]

Thank you guelphdad for joining in this thread...

I have tested both:

PHP Code:

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field != 'NULL' GROUP by $field ORDER by COUNT($field) DESC LIMIT 6";

and

$qry = "SELECT $field, COUNT($field) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT($field) DESC LIMIT 6"; 


and they BOTH give me the same result:

Entertainer of the year award UK:
Henry Smith (6)
Gary Perkins (3)
John Permentor (2)
Darren Busby (2)
Richard Palmer (1)

Total votes = 14

Please understand, I'm NOT arguing with anybody, I'm just trying to learn and understand, being a relative novice.
Can you explain what the difference is between the two?
I have used !='???' prevoiusly, quite sucessfully:

PHP Code:

$qry = "SELECT * FROM ";
$qry .= $vars["table directory"];
$qry .= " WHERE category = 'ag'";
$qry .= "AND status != 'suspended' ";
$qry .= "AND status != 'unreviewed' ";
$qry .= " ORDER BY url DESC, name ASC" ; 


I have used this code in my countrymusic.org.uk directories, in all categories.

Old Pendant:

I tested this code:

PHP Code:

$qry = "SELECT $field, COUNT(*) AS theCount FROM $table " 
     . " WHERE $field IS NOT NULL "
     . " GROUP by $field "
     . " ORDER by theCount DESC LIMIT 6"; 


and it returned:

Entertainer of the year award UK:
Henry Smith ()
Gary Perkins ()
John Permentor ()
Darren Busby ()
Richard Palmer ()

Total votes = 14

I do want to thank you both for your input.
I have learned quite a lot while doing this project.

THANK YOU

[guelphdad]

You are not understanding, it isn't the sql but your php code that is poorly written.
At no point in your code will
COUNT(*) AS theCount
be printed out because nowhere in your php loop do you refer to theCount in order for it to be printed out.

In your WHILE loop you refer to this:
$list = $row[''.$field.''];

and THAT will never refer to theCount.

$qry = "SELECT $field as entertainer, 
COUNT(*) as theCount 
FROM $table 
WHERE entertainer IS NOT NULL 
GROUP BY 
  entertainer 
ORDER by 
  thecCount DESC 
LIMIT 6";  

while($row = mysql_fetch_array($results)){ 
  printf("%s (%d)", $row['entertainer'],$row['theCount']
}

in my code above I've skipped the part pushing the query in to $results. but you'll see how I refer to theCount in output part of the php WHILE.

In your query above you refer to $field which is fine EXCEPT you never refer to theCount in your output so therefore it will always show up blank.

So it isn't COUNT(*) and COUNT($field) that are just different (in that the latter skips counting NULL rows) it is that you never actually refer to the output of COUNT(*) when you used it that way.

[Old Pedant]

Yes. Your PHP code does

$list = $row[''.$field.''];
    $main_content .= '<b><font size=2>'.$list.' ('.$row['COUNT('.$field.')'].')</b>';
    $main_content .= '<br />';

See?

You were lucky that PHP/MySQL allows that to work. Some languages and some databases don't carry an un-aliased field like that from DB to language. It's almost always a better idea to use an alias. (" ... AS theCount") and then use the alias in the PHP code.

If, for example, you did something like

SELECT SUM( IF(field IS NULL,0, field ) ...

it would be necessary. So good to get in the habit.

[countrydj]

Hi Guys...

Thanks very much for trying to 'teach' me. I really do appreciate it.
If at any time you get fed up, please say so.
At leats I have the script working, albeit somewhat with 'poor' php, due to my inexperience.

I'm afrad that I have been experimenting all morning and getting nowhere.

This code, by guelphdad works:

PHP Code:

while($row = mysql_fetch_array($results)){ 
  printf("%s (%d)", $row['entertainer'],$row['theCount']

btw; I added ); to the end of the line to make it work:

  printf("%s (%d)", $row['entertainer'],$row['theCount']); 


but I need it to work with

PHP Code:

        $main_content .= 

instead of 

printf 


to work with the rest of my script.
This is where I have spent most of my time, trying to figure out the correct coding.

Can you advise me please ???

[Old Pedant]

    $main_content .= $row['entertainer'] . "(" . $row['theCount'] . ")<br/>\n";

or something along those lines.

But why? Creating a big long $main_content string only to then later do

    echo $main_content;

is *SLOWER* than simply echoing (printing, whatever) each line as you come to it.

[countrydj]

Is this not virtually the same as what I had originally ???
Your code:

PHP Code:

    $main_content .= $row['entertainer'] . "(" . $row['theCount'] . ")<br/>\n"; 


My original code:

PHP Code:

    $main_content .= '<b><font size=2>'.$list.' ('.$row['COUNT('.$field.')'].')</b>'; 


My NEW code:

PHP Code:

$field = "entertainer"; // This is specified at the top of each category, which means that this is the only variable that I have to change in each category. There is currently 21 categories.
$list = $row[''.$field.''];

 $main_content .= "<font size=2>".$list . "(" . $row['theCount'] . ")<br/>\n"; 


NB. The reason for using $list is so that I do not have to edit all the other categories.

The only change, that I can see, is $row['theCount']

I thought the idea of improved php code was to use ("%s (%d)", as in:

PHP Code:

("%s (%d)", $row['entertainer'],$row['theCount']); 


You suggested:

You were lucky that PHP/MySQL allows that to work. Some languages and some databases don't carry an un-aliased field like that from DB to language. It's almost always a better idea to use an alias. (" ... AS theCount") and then use the alias in the PHP code.

I still don't understand the principle of using ("%s (%d)", , and I can't find a satisfactory answer on Google.
The idea of usng $main_content .= is that at the end of the script I have this code:

PHP Code:

$template = str_replace("[main content]",$main_content,$template);
print $template; 


This way I can change the template as required.

Finally, I want to thank you, and guelphdad for the help and advice that you have given me. I certainly have a better general understanding of PHP/MySql now.

[countrydj]

Hi Guys...
Since my last post, I have been experimanting, and Googling.
This is what I found:
If I change guelphdad code:

PHP Code:

  printf("%s (%d)", $row['entertainer'],$row['theCount'] 


to

PHP Code:

 $main_content .=   sprintf("%s (%d)", $row['entertainer'],$row['theCount'] 


My script works fine.
In order to format it how I want it, this is the code that I have modified to:

PHP Code:

$field = "entertainer"; // This is specified at the top of each category, which means that this is the only variable that I have to change in each category. There is currently 21 categories. 
$list = $row[''.$field.'']; 


    $main_content .= sprintf("<font size=2>%s (%d)<br />", $list,$row['theCount']); 


I'm starting to understand a little bit better now.
Here is the link that I read:
http://www.php.net/manual/en/function.sprintf.php

This is all down to you Guys...

THANK YOU

You two are among the GREATEST !!!!