Dynamically made SQL queries using PDO bound parameters

PHP
1

I’ve come up with the following way to create dynamic query (by adapting some code I found). My question is: can it be improved some way? Is this the most efficient way to do it?


// build the query string
unset($sql);

if ($age) {
	$sql[] = "age = :age";
}
if ($height) {
	$sql[] = "height = :height";
}

$query = "UPDATE user_stats SET ";

if (!empty($sql)) {
	$query .=  implode(', ', $sql) . ' WHERE id_user = :id_user';
}

// for pdo->execute($params)

$params = array();

if ($age) {
$params = array_merge($params, array(':age' => '39'));
}

if ($height) {
$params = array_merge($params, array(':height' => '72in'));
}

Of course I will have a check to make sure the query or parameters aren’t empty. But is this the right way to proceed on this? Or is there a better way? I am trying to make a form that sends info to the db, but the form itself has optional parts. Because the parts are optional, I have made an individual query for each part, but I want to just perform one query in total.

2

I’d say it can be slightly improved. The first thing I’d do is set $sql to an empty array, rather than unsetting it (causing the variable to be no longer defined). Otherwise you’re pushing onto an undefined variable, which whilst still valid PHP code, is not as efficient as pushing onto a defined array.

You can also remove some of those duplicated IF statements by building the parameters to be bound alongside the actual SQL query.


$sql = $params = array();

if ($age) {
    $sql[] = "age = :age";
    $params = array_merge($params, array(':age' => '39'));
}
if ($height) {
    $sql[] = "height = :height";
    $params = array_merge($params, array(':height' => '72in'));
}

$query = "UPDATE user_stats SET ";

if (!empty($sql)) {
    $query .=  implode(', ', $sql) . ' WHERE id_user = :id_user';
}
3

My question is: can it be improved some way?

there are some DB libraries out there that provide you with shorthand methods so you don’t have to write them yourself. (e.g. doctrine/dbal)

4

Just to reinforce what @Dormilich said, with Doctrine 2’s database access layer (http://doctrine-dbal.readthedocs.org/en/latest/reference/data-retrieval-and-manipulation.html#update) you can do:


$conn->update('user', array('username' => 'jwage'), array('id' => 1));

// Which yields:
// UPDATE user (username) VALUES (?) WHERE id = ? (jwage, 1)

Even if you chose not to go with doctrine 2 you might want to look at their design and see if you can generalize your code a bit. In particular, use named parameters (which is really a PDO thing) is generally more complicated then using ? placeholders.

5

Thanks tpunt - I wonder why I didn’t consider that! I think it was because I was focusing on each task individually. But you’re right - combining the two is certainly more efficient, and easier to maintain, too.

I haven’t really looked at doctrine/dbal before, but I guessed this was a use scenario. Thanks to both of you for pointing it out and for the good example. When things start to become harder, I will know where to look.

6