PHP PDO - Dynamic SQL prepare statement does not work like hard coded

Question

I coded a mini-DBAL and here is a part of that.

private function setWhere($conditions) {
    if (count($conditions)>0) {
        $where = '1=1';
        $params = [];

        $ret = $this->iterateWhere($where, $conditions, $params, 'AND', '=');
        return [
                    'where' => 'WHERE '.str_replace('1=1 OR ', '', str_replace('1=1 AND ', '', $ret['where'])),
                    'params' => $ret['params'],
                ];
    } else
        return [
                    'where' => '',
                    'params' => [],
                ];
}

private function iterateWhere($where, $conditions, $params, $logic, $op) {
    //go through given set of conditions
    foreach ($conditions as $condition=>$value) {
        //check for lowest condition
        if (is_array($value)) {
            //check for logic or operator condition
            if (in_array($value[0], ['AND', 'OR', 'and', 'or'])) {
                //logic
                $where .= ' '.$logic.' (1=1';
                $ret = $this->iterateWhere($where, $value, $params, $value[0], $op);
                $where = $ret['where'];
                $params = $ret['params'];
                $where .= ')';
            } else {
                //operator
                foreach($value as $k=>$v) {
                    if ($k != '0') {
                        $where .= ' '.$logic.' ('.$k.$value[0].':'.count($params).')';
                        $params[] = $v;
                        break;
                    }
                }
            }
        } else {
            if ($condition != '0') {
                $where .= ' '.$logic.' ('.$condition.$op.':'.count($params).')';
                $params[] = $value;
            } else {
                if (in_array($value, ['AND', 'OR', 'and', 'or']))
                    $logic = strtoupper($value);
                else
                    $op = strtoupper($value);
            }
        }
    }

    return [
                'where' => $where,
                'params' => $params,
            ];
}

public function getTableCol($table, $column, $conditions) {
    try {
        $condition_part = $this->setWhere($conditions);
        $stmt = $this->pdo->prepare('SELECT '.$column.' FROM '.$table.' '.$condition_part['where']);

        foreach ($condition_part['params'] as $param=>$pVal) {
            switch (strtolower(gettype($pVal))) {
                case 'string':
                    $stmt->bindParam(':'.$param, $pVal, \PDO::PARAM_STR);
                    break;

                case 'integer':
                    $stmt->bindParam(':'.$param, $pVal, \PDO::PARAM_INT);
                    break;

                case 'float':
                case 'double':
                    $stmt->bindParam(':'.$param, $pVal);
                    break;

                case 'boolean':
                    $stmt->bindParam(':'.$param, $pVal, \PDO::PARAM_BOOL);
                    break;

                case 'null':
                    $stmt->bindParam(':'.$param, $pVal, \PDO::PARAM_NULL);
                    break;

                default:
                    die('Unhandled param type for \''.$pVal.'\'');
                    break;
            }
        }

        $stmt->execute();
        $ret = $stmt->fetchAll(\PDO::FETCH_COLUMN, 0);

        return [
                    'rows' => count($ret),
                    'result' => $ret,
                ];
    } catch (\PDOException $e) {
        return [
                    'rows' => 0,
                    'result' => $e->getMessage(),
                ];
    }
}

I call my function like so:

$client_list = $db->getTableCol("cs_client", "client_id", ["domain" => "PB", "name" => "My Client"]);

I find my code populating the SQL and parameters properly, but it does not return any rows when I pass more than one WHERE condition. If I manually hardcode the dynamically prepared statement, then it works. I am not able to figure out why.

Here is some outputs from echo and print_r:

SQL passed into prepare():

SELECT client_id FROM cs_client WHERE (domain=:0) AND (name=:1)

Array output of params:

Array
(
    [0] => PB
    [1] => My Client
)

Sequence in for loop to bind param:

0 => PB
1 => My Client

To re-iterate, if I copy-paste that prepared statement manually into '$stmt = $this->pdo->prepare("")' and then bind :0 and :1 using the output values, it returns the only row in that table.

If I pass only one (either of the two) or no conditions, then it returns the row, but not if I pass two conditions, although it populates the conditions right.

Shouldn't matter much, but I am using MS SQL Server 2014.

Answer 1

I guess I was trying too much. Instead of using count() to increment named parameters as 0, 1, 2, 3... I simply used '?' and passed the list of parameters into the execute statement like so:

$stmt->execute($condition_part['params']);

Makes for a smaller code, since I didn't need the loop to iterate through the list of paramters.