SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Count 2 Items (Database Query)

    This query works:

    PHP Code:
    $result mysql_result(mysql_query("SELECT COUNT(Name)
    FROM gz_mammals
    WHERE Name = '
    $MyURL'"),0); 
    How do I modify it so it counts TWO fields? The following query doesn't work...

    PHP Code:
    $result mysql_result(mysql_query("SELECT COUNT(Name, NameCommon)
    FROM gz_mammals
    WHERE Name = '
    $MyURL' OR NameCommon = '$MyURL'"),0); 
    However, it works if I replace "Name, NameCommon" with an asterisk.

    Thanks.

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Maybe, count(Name)+count(NameCommon)
    Saul

  3. #3
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What exactly are you trying to count? I suspect you're looking for the number of unique Name and unique NameCommon values; if so, this should work:
    Code:
    SELECT COUNT(DISTINCT Name), COUNT(DISTINCT NameCommon)
    FROM gz_mammals
    WHERE Name = '$MyURL'
    PHP questions? RTFM
    MySQL questions? RTFM

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    What exactly are you trying to count? I suspect you're looking for the number of unique Name and unique NameCommon values; if so, this should work:
    Code:
    SELECT COUNT(DISTINCT Name), COUNT(DISTINCT NameCommon)
    FROM gz_mammals
    WHERE Name = '$MyURL'
    It's for a dynamic page on a CMS. I want to display, for example, an article about canids (family Canidae), whether visitors type in the scientific name (MySite/Life/Canidae) or the common name (MySite/Life/Canids).

    The rest of my script looks like this:

    PHP Code:
    switch($result)
    {
     case 
    1:
     (include 
    article)
     break;

     case 
    0:
     (include 
    error 404 page)
     break;

     default:
     (include 
    dupe page)
     break;

    Anyway, your tip seems to do the trick...

    Code:
    SELECT COUNT(DISTINCT Name), COUNT(DISTINCT NameCommon)
    FROM gz_mammals
    WHERE Name = '$MyURL' OR NameCommon = '$MyURL'
    Thanks.

  5. #5
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see where you're heading with this. You then want this:
    Code:
    SELECT COUNT(*)
    FROM gz_mammals
    WHERE Name = '$MyURL' OR NameCommon = '$MyURL'
    You don't in fact care how many rows there are, just that there is at least one, correct? Then why waste database server resources counting each type individually? Count them up together - your WHERE clause is taking care of checking whether the entry matches Name of CommonName, you don't in fact need to count those columns at all.
    PHP questions? RTFM
    MySQL questions? RTFM

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I'll try it that way. Thanks for the tips.


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
  •