3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – Mario Dec 29 '14 at 8:27
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.