Dynamically add WHERE parameters to a PDO sql statement

Question

I need to build an SQL statement in PHP using PDO and the number of values in the WHERE clause are dynamic.

e.g.

SELECT column1, column2 FROM table WHERE category = :category OR category = :category

In this case there are 2 categories in the where clause I'm comparing, but it could be more than 2 so I can't just use :category1 and :category2 as the names.

I tried the following, but it's giving a "General error: 2031" message

  $where = array("category = :category", "category = :category");
  $params = array("abc", "xyz");
  $query = "SELECT column1, column2 FROM table WHERE ". implode(" OR ", $where);
  $s= $h->prepare($query);
  $s->setFetchMode(PDO::FETCH_ASSOC);
  $s->execute(array_values($params));

Any suggestions?

score 0 · Answer 1 · edited May 23 '17 at 12:25

0

If there are many categories, use a SQL IN statement

You can look here for how to bind it to your array: Can I bind an array to an IN() condition?

edited May 23 '17 at 12:25

Community

1
1

answered Apr 18 '17 at 13:45

blue112

52,634
3
45
54

Thanks for the quick replies. I'll look into the IN() condition. – user1480951 Apr 18 '17 at 13:49
1

This should have been posted as a duplicate flag, not as an answer. – Shadow Apr 18 '17 at 13:49
@Shadow If OP doesn't know about the `IN` statement he can't search for how to bind parameters to it. – blue112 Apr 18 '17 at 13:51
2

The fact that the OP could not have found the other topic through a search has nothing to do with its question being a duplicate. Marking a question as a duplicate of another one tells the OP that his/her question has already been answered here on SO. – Shadow Apr 18 '17 at 13:53

score -1 · Answer 2 · edited Apr 18 '17 at 14:05

-1

See above comment regarding IN statement, but in case if you still plan to use dynamically generated where part you should either use question mark (?) or create the unique name for each PDO pseudo variable:

$where = array("category = ?", "category = ?");
$params = array("abc", "xyz");
$query = "SELECT column1, column2 FROM table WHERE ". implode(" OR ", $where);
$s= $h->prepare($query);
$s->setFetchMode(PDO::FETCH_ASSOC);
$s->execute($params);

Read here for more details: http://php.net/manual/en/pdo.prepare.php

edited Apr 18 '17 at 14:05

Your Common Sense

156,878
40
214
345

answered Apr 18 '17 at 13:47

Denis O.

1,841
19
37

you are partially right with your statement but you should pay more attention to the code you are posting – Your Common Sense Apr 18 '17 at 13:50

Dynamically add WHERE parameters to a PDO sql statement

2 Answers2