PHP PDO with dynamically created where statements

Question

I'm trying to convert mysqli_query to PDO.

I got stuck with my dynamically created WHERE clausule. I read this post (PHP PDO dynamic WHERE clause), but there you lose the prepare function of PDO, and that is what I want to do.

This is (part of) my origional code

// this is the '$_POST' array
$_POST['org'][0] = 1;
$_POST['org'][1] = 2;
$_POST['org'][2] = 5;

/* Execute a prepared statement by passing an array of values */
$sql['select'] = "SELECT * FROM `table` ";

if(isset($_POST['org']) AND count($_POST['org']) > 0) {  
  $sql['where'] .= "WHERE ( `org_id` = '".mysqli_real_escape_string($conn, $_POST['org'][0])."' ";

  foreach ($_POST['org'] as $key => $value) {
    if($key !== 0)
      $sql['where'] .= "OR `org_id` = '".mysqli_real_escape_string($conn, $value)."' ";
  }
}

$sql = $sql['select'] . $sql['where'];
$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));

How can I use prepared statements in the foreach loop?

d0niek · Accepted Answer · 2019-12-18 20:02:33Z

1

You should build your query and instead of using values inside the query you should use placeholders (? or :placeholder_name):

$query = 'SELECT * FROM table WHERE col IN (?,?,?)';
$stmt = $pdo->prepare($query);
$stmt->execute($_POST['org']);

Go and read https://www.php.net/manual/en/pdo.prepare.php#refsect1-pdo.prepare-examples for more examples

answered Dec 18, 2019 at 20:02

d0niek

2381 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Franq · Accepted Answer · 2019-12-18 20:23:10Z

1

Ah!

This is what I did now, and it works. Thanks.

$sql['where'] .= "WHERE ( `org_id` = ? ";
$values[] = $_POST['org'][0];

foreach ($_POST['org'] as $key => $value) {
  if($key !== 0) {
    $sql['where'] .= "OR `org_id` = ? ";
    $values[] = $value;
  }
}

$sql['where'] .= ")";

$sql = $sql['select'] . $sql['where'];

$stmt = $pdo->prepare($sql);
$stmt->execute((array) $values);

Thank you!

answered Dec 18, 2019 at 20:23

Franq

233 bronze badges

4 Comments

tadman Over a year ago

Technically true but using named placeholders and building an associative array makes this a lot more flexible and is only slightly more work.

spencer7593 Over a year ago

a note on style: my preference is to include the required leading space before the WHERE or OR along with the keyword, rather than requiring preceding code to include an extra trailing space at the end. e.g. .= " WHERE ... and .= " OR ... Just makes more sense to me. When looking at the code that sets up the first part of the select, it's not at all obvious why a trailing space is needed.But looking at the code that appends the WHERE, its more obvious why there needs to be space before the WHERE keyword,... seems like we should do it there, rather than backtrack to other code

Franq Over a year ago

@tadman what do you mean? Can you give a little example of that?

tadman Over a year ago

If you use named placeholders that match your args, you can just execute($_POST) or execute($_POST['org']) or whatever. You should also consider org_id IN (?, ?, ...) instead of org_id=? OR org_id=? OR .... The answer by d0niek incorporates both these elements.

Collectives™ on Stack Overflow

PHP PDO with dynamically created where statements

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related