SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: Using "COUNT"

  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using "COUNT"

    I know this is a really simple question, but for some reason I just can't get the MySQL "COUNT" thing to work for me. Can someone please show me how to use the count feature (such as mysql_query("COUNT(*) FROM news WHERE id = 5") or something along those lines), then take the result so it can be displayed on the site.

    Sorry if this is an elementary problem, my mind has gone blank.
    Regards, Ant.

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its still a SELECT statement, and you should alias any function you do

    So Select Count(*) as recordcount from news where ID='5'
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I forgot to type that in. I do have SELECT in there already. Here's what I have at the moment:

    PHP Code:
       $comment_count_sql "SELECT COUNT(News_Comment_ID) AS comment_count FROM newscomments WHERE NID = ";
    $comment_count_sql .= $latest_news["News_ID"];
    $comment_count mysql_query($comment_count_sql); 
    $comment_count $comment_count["comment_count"]; 
    Regards, Ant.

  4. #4
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting or die(mysql_error()); at then end.
    This will not solve the problem but it will tell you if there's something wrong with the query.

    PHP Code:
    $query mysql_query("SELECT COUNT(*) AS 'comment_count' FROM newscomments WHERE NID = {$latest_news['News_ID']}") or die(mysql_error());

    $total mysql_fetch_row($query);
    print_r($total); 
    I think on problem with your code is that you don't use fetch_row / array to actually get the data.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by aspen
    Select Count(*) as recordcount from news where ID='5'
    Please do not enclose numeric values (e.g. auto_increment integers) in single or double quotes.

  6. #6
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?

    If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

    Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by aspen
    Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?
    In the application which sends the data.

    Originally posted by aspen
    If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

    Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.
    Well, you're relying on:
    1) non-standard, undocumented behavior and
    2) a side effect of MySQL-induced laziness.

    If you look up numeric syntax in the docs, it says nothing about enclosing them in quotes:
    http://www.mysql.com/doc/N/u/Number_syntax.html

    I actually can't find in the documents where it allows numeric to string conversion on the fly but I assume it has to do with this page:
    http://www.mysql.com/doc/D/e/Design_Limitations.html

    Specifically:
    If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. For numerical values this is 0, the smallest possible values or the largest possible value. For strings this is either the empty string or the longest possible string that can be in the column.
    It sees you are comparing int and char, so it will convert the char to an int in order to follow their ‘ignore problems’ mantra.

    The problem with this is, since it is undocumented and relies on the assumption that MySQL will ‘parse out’ everything but the initial $ID val there is a chance that they could change the behavior without notifying anyone (since you shouldn’t be doing this, they are more than in their right to do so).

    Plus, it should be your application’s job to not allow tainted data to hit the DB. Simply run it through intval( $var ) in PHP, or some other likeness in another programming language.

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, there is this here:
    http://www.mysql.com/doc/C/a/Cast_Functions.html

    To cast a string to a numeric value, you don't normally have to do anything; Just use the string value as it would be a number:

    mysql> SELECT 1+'1';
    -> 2
    It, however, does not say what 'expected' results would be to non-numeric data in the enclosed quotes...

    What would happen if someone instead passed:

    $var = "1\'whatever\'";

    That should escape the single quotes to allow something in there, no?

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by aspen
    Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?

    If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

    Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.
    even putting quotes around the data won't solve the problem. you need to addslashes() (if magic_quotes_gpc is off, which it should be) to string data. so just use a different function for numerical data. if someone enters "" or "abc", it will just be turned into a 0 which won't cause any problems in the query. you can use the intval() function, like MattR said, or i prefer to type-cast the variable with (int) since it's a bit faster and less to type.

    PHP Code:
    $id = (int) $_GET['id'];
    // OR
    $id intval($_GET['id']); 
    just another part of good programming and verifying user data. no quotes around numbers in any of my queries.

    P.S. what's even funnier is when people quotes numbers that aren't even from a variable, just a number that they hard-coded in the query.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  10. #10
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, erm, how do I count the number of rows?!?!?!!!!!
    Regards, Ant.

  11. #11
    SitePoint Zealot Klav's Avatar
    Join Date
    Mar 2002
    Location
    Huddersfield, UK
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Couldn't you just use mysql_num_rows()?

  12. #12
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Klav
    Couldn't you just use mysql_num_rows()?
    Yes, but that first requires a MySQL Query. That means two processes are done, instead of just one when using COUNT. That means more load on the processor, not much, but it is more.
    Regards, Ant.

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So why doesn't this work?

    PHP Code:

    $result 
    mysql_query"SELECT COUNT( * ) AS comments
                              FROM newscomments
                             WHERE NID = 
    $latest_news['News_ID']" );

    $array mysql_fetch_array$result );

    echo 
    "Your count is $array[comments]"

  14. #14
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That set me off in the right direction!

    Thanks

    I got it working with this:

    PHP Code:
       while ($latest_news mysql_fetch_array($get_latest_news)) {
       
    $comment_count_sql "SELECT COUNT(News_Comment_ID) AS comment_count FROM newscomments WHERE NID = ";
       
    $comment_count_sql .= $latest_news["News_ID"];
       
    $comment_count mysql_query($comment_count_sql); 
       
    //$comment_count = $comment_count["comment_count"];
       

    $array mysql_fetch_array$comment_count );

    $comment_count $array["comment_count"]; 
    Regards, Ant.


Bookmarks

Posting Permissions

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