I'm moving a system over to PDO and since the queries are parameterized, I need to specify both the type and value of each parameter when preparing them.
Right now I have a prepare method to create the PDOStatement object:
/**
* Prepares a statement
* @param string $sql The query
* @param array $data The data to bind
* @param array $types The types of data
* @return \PDOStatement
*/
private function prepare($sql, array $data, array $types) {
if (sizeof(array_diff_key($types, $data)) || sizeof(array_diff_key($data, $types))) {
throw new \InvalidArgumentException('Keys of data and types arrays do not match.');
}
$stmt = $this->pdo->prepare($sql);
foreach ($data as $key => $value) {
$stmt->bindValue($key, $value, $types[$key]);
}
return $stmt;
}
Places where the code is called look like this:
$sql = "UPDATE table_name SET col1 = :param1, col2 = :param2, col3 = :param3 WHERE primary_key = :id;";
$data = array('param1' => $param1, 'param2' => $param2, 'param3' => $param3, 'id' => $id);
$types = array('param1' => \PDO::PARAM_STR, 'param2' => \PDO::PARAM_STR, 'param3' => \PDO::PARAM_INT, 'id' => \PDO::PARAM_INT);
static::$db->prepare($sql, $data, $types)->execute();
It feels a bit cumbersome to specify the data and types in two separate arrays. Is there any way I can improve this?
I could make a SQLParameter class like this:
class SQLParameter {
public $name;
public $value;
public $type;
public function __construct($name, $value, $type) {
$this->name = $name;
$this->value = $value;
$this->type = $type;
}
}
But this would make the situation even worse.
