2

Lets say I have a mysql query which gets built depending on certain conditions: example

$query = "SELECT * from `usertable` where users_active=:users_active";
if($mode=="archived") {
    $query .= " AND archived=:archived";
}

$stmt = $dbpdo->prepare($query);
$stmt->bindParam(':users_active', $users_active);
$stmt->bindParam(':archived', $archived);
$stmt->execute();

Now, if I run the above it will only work if $mode=="archived", as otherwise the named placeholder ":archived" will not be part of the query.

This makes sense to me in one respect but begs the question of how to deal with it elegantly. I have lots of queries in my application which are built conditionally. I could do this, but this seems like duplication to me:

if($mode=="archived") {
    $stmt->bindParam(':archived', $archived);
}

This seems long winded, particularly if there are lots of conditions to how the query is built. Does anyone have a neater idea on how to do this without having to add lots of conditional tests.

Any thoughts would be appreciated.

Regards

James

James Stoddern
  • 397
  • 1
  • 5
  • 19
  • What is wrong with that you have tried (conditionally adding params). I think if the code is working then it should not be a problem. – Maximus2012 Apr 07 '15 at 18:05
  • 1
    Please add your full code how you build your querys – Rizier123 Apr 07 '15 at 18:05
  • 1
    See my Answer [**here**](http://stackoverflow.com/questions/27097560/writing-a-query-that-contains-variable-where-based-on-user-input/27103625#27103625) on building dynamic queries – david strachan Apr 07 '15 at 21:03

1 Answers1

7

You can use an array with the values and send that as a parameter to the execute() method.

If the type casting of the variables that bindParam() offers is not that important (you're not even using it...), that makes building queries a lot easier as you can fill the array when you build the query string.

For your example:

$query = "SELECT * from `usertable` where users_active=:users_active";
$params = array(':users_active' => $users_active);

if($mode=="archived") {
    $query .= " AND archived=:archived";
    $params[':archived'] = $archived;
}

$stmt = $dbpdo->prepare($query);

$stmt->execute($params);
jeroen
  • 91,079
  • 21
  • 114
  • 132
  • Well if we would see OP's full code we could maybe even optimize the if statements – Rizier123 Apr 07 '15 at 18:12
  • @Rizier123 That's true, I was just using the OP's example to show you don't need any additional `if` statements. I use it like this a lot :-) – jeroen Apr 07 '15 at 18:13
  • Depending on OP's full code he may don't even need any if statements OR at least he doesn't have to write more and more. Let's see what OP says, I'm still waiting for OP's code: http://stackoverflow.com/questions/29498155/pdo-bind-params-depending-on-whether-they-exists-in-the-query#comment47155024_29498155 :) – Rizier123 Apr 07 '15 at 18:18