I'm pulling some data from mysql and counting occurrences of matches (seemingly equivalent to WHERE foo='bar'). However, as I loop thru the data in PHP, my count is much lower than the count in mysql.

MYSQL> SELECT COUNT(foo) FROM database.table WHERE foo='bar';

# PHP
while ($response = mysql_fetch_assoc($surveydata)){
    if ($response==='bar') {
        $bar++;
    }
}

The data might contain one or more &, so I want to match only bar and not bar & foobar. I suspect mysql is counting bar and bar & foobar whereas php is only counting bar and not bar & foobar. Php is returning 1210, and mysql is returning 1783, so it's exactly practical to manually count to see who is right…

I googled around, but was surprised nothing relevant came up for "mysql exact match" or "mysql exactly equal" x,x

EDIT

here's the actual sql

SELECT COUNT(`race`) FROM `database`.`table` WHERE `completion status`='complete';

and the actual php

mysql_query("SELECT `race`,`etcetera` FROM `database`.`table` WHERE `completion status`='complete';");

$demographics=array(
    "race"=>array(
        "White"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Black"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Hispanic"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Asian"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Pacific Islander"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Multiracial"=>array('consented'=>0,'partial'=>0,'completed'=>0),
        "Other"=>array('consented'=>0,'partial'=>0,'completed'=>0)
    )
    //more
);

while ($dbrecord = mysql_fetch_assoc($surveydata)) {
    foreach ( $dbrecord as $dbfield=>$dbcellval ) {
        foreach ( $demographics as $demographic=>&$options ) {
            foreach ( $options as $option=>&$counter ) {
                if ( $option==="Multiracial" && strpos($dbcellval,'&') >0 && strpos($dbcellval,'&')!==false ) {
                    if ($dbrecord['consent']==="1"){
                        $demographics["race"]["Multiracial"]['consented']++;
                        if ($dbrecord['completion status']==="partial") {
                            $demographics["race"]["Multiracial"]['partial']++;
                        } // if
                        else if ($dbrecord['completion status']==="complete") {
                            $demographics["race"]["Multiracial"]['completed']++;
                        } // else if
                    } // if
                }
                else if ($option===$dbcellval){
                    if ($dbrecord['consent']==="1"){
                        $counter['consented']++;
                        if ($dbrecord['completion status']==="partial") {
                            $counter['partial']++;
                        } // if
                        else if ($dbrecord['completion status']==="complete") {
                            $counter['completed']++;
                        } // else if
                    } // if
                } // else if $option==$item
            } // foreach $options
        } // foreach $demographics
    } // foreach $dbrec开发者_运维百科ord
} // while

the data from SELECT race FROM database.table looks like:

White & Black
White
White & Asian
White & Asian & Black
Asian
Asian & Black
// etc

You can do this:

MYSQL> SELECT COUNT(foo) FROM database.table WHERE BINARY foo='bar';

BINARY does the magic!!!

If you want to count the records where foo is exactly bar, your SQL query is correct.

There's something wrong with your PHP code and the code you posted shouldn't work at all (should count 0 records).

MYSQL> SELECT COUNT(foo) AS rows FROM database.table WHERE foo='bar';

$response = mysql_fetch_assoc($surveydata);
echo $response['rows'];