CODEWITHSUNDEEP
mysqlpdo
Franq
Franq

Reputation: 23

PHP PDO with dynamically created where statements

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?

Upvotes: 1

Views: 263

Answers (2)

Franq
Franq

Reputation: 23

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!

Upvotes: 1

d0niek
d0niek

Reputation: 228

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

Upvotes: 1

Related Questions