PHP associative array with two properties

Question

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.

• Mario: Haven't worked that much with PDO stuff to be honest, but can't you just use the implicit conversion and pass everything quotes/as a string?

Answer 1

I can immediately see two more options, perhaps they're something you're after, perhaps not.

Option 1

Turn $data into a multidimensional array. You would end up with something such as:

...array('param1' => [$param1, \PDO::PARAM_STR], 'param2' => [$param2, \PDO::PARAM_STR]...

Pros

• Both value data and type are paired to the key, so now you can simply refer to either with array referencing.
• You can play with indentations and returns to find a format that suits you. It's easy enough to separate each key/value onto a new line.

Cons

• It's more condensed, which might lead to readability issues. (Do you keep in mind the 2nd pro above though)
• An IDE's autoformat might place it all on one line, depending on your settings.

Option 2

Have a type searching function to automatically determine the type and assign it. This could be a simple helper function with a few conditionals, each with one of the is_* variable functions.

You would want to call the function as a parameter of bindValue().

Pros

• This would scale quite well.
• It's easy to alter outputs and fine-tune.
• You won't have to add the type every time you add a new key/value pair.
• Can even check for things such as objects and handle those properly.

Cons

• More business code and less model/data code.

---

I would agree that the SQLParameter is overboard. If anything, maybe one of my solutions will inspire your own.