Select Count 2 Items (Database Query)

[geosite]

This query works:

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

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

[php_daemon]

Maybe, count(Name)+count(NameCommon)

[kromey]

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:

SELECT COUNT(DISTINCT Name), COUNT(DISTINCT NameCommon)
FROM gz_mammals
WHERE Name = '$MyURL'

[geosite]

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:

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:

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...

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

Thanks.

[kromey]

Ah, I see where you're heading with this. You then want this:

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.

[geosite]

OK, I'll try it that way. Thanks for the tips.