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