CODEWITHSUNDEEP
phpmysqlpdo
pokey
pokey

Reputation: 29

MySQL PDO count function returning incorrect with multiple WHERE criteria

As long as I only specify one item in the WHERE clause, this function works fine. If there's more than one, it always returns 0.

Here's the function:

function count_rows($table, $where=array()){
    $sql = "SELECT COUNT(*) FROM `$table`";
    if(!empty($where)){
        $sql .= " WHERE (";
        foreach($where as $key=>$value){
            $sql .="`". $key . "`=:w_" . $key . " AND ";
        }
        $sql = substr($sql, 0, -4);
        $sql .= ") ";
    }
    $stmt = $this->conn->prepare($sql);
    foreach($where as $key=>$value){
        $stmt->bindParam(":w_".$key, $value);
    }
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $stmt->execute();
    $this->stmt = $stmt; 
    return $this->stmt->fetchColumn();

}

For example, the following returns the number of rows where list_id is set to $list_id:

$email_count = count_rows("emails", array("list_id"=>$list_id));

Using two criteria in the WHERE clause, however, causes it to return 0 no matter what:

$optout_count = count_rows("emails", array("list_id"=>$list_id, "optout"=>"1"));

I've tried with and without the parentheses around the WHERE clause, and the debug function I use shows the query properly. I have also tried putting quotes around the values in the array. Any help would be appreciated.

Upvotes: 2

Views: 112

Answers (1)

xdazz
xdazz

Reputation: 160853

PDOStatement::bindParam bind a reference to a variable. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Solution:

Just change 

$stmt->bindParam(":w_".$key, $value);

to 

$stmt->bindValue(":w_".$key, $value);

Upvotes: 3

Related Questions