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.
Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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....
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  2. #17
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  3. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Indeed. Why did you change from
    Code:
    SELECT $field, COUNT(*) FROM $table WHERE $field IS NOT NULL GROUP by $field ORDER by COUNT(*) DESC LIMIT 6";
    to
    Code:
    $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.
    Code:
    $sql = "SELECT $field, COUNT(*) AS theCount FROM $table " 
         . " WHERE $field IS NOT NULL "
         . " GROUP by $field "
         . " ORDER by theCount DESC LIMIT 6";
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  4. #19
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  5. #20
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    $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.

  6. Users who have thanked guelphdad for this post:

    countrydj (07-31-2012)

  7. #21
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Yes. Your PHP code does
    Code:
    $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
    Code:
    SELECT SUM( IF(field IS NULL,0, field ) ...
    it would be necessary. So good to get in the habit.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  8. #22
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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']

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

  9. #23
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Code:
        $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
    Code:
        echo $main_content;
    is *SLOWER* than simply echoing (printing, whatever) each line as you come to it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  10. #24
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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.
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club

  11. #25
    Regular Coder
    Join Date
    Nov 2011
    Location
    Preston, UK
    Posts
    131
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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 !!!!
    The MAN, The MYTH, The LEGEND:
    John C
    ________________________________
    Support your local Country Music Club


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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