PHP: prepared statement, IF statement help needed

Question

I have the following code:

$sql = "SELECT name, address, city FROM tableA, tableB WHERE tableA.id = tableB.id";

if (isset($price) ) {
    $sql = $sql . ' AND price = :price ';
}
if (isset($sqft) ) {
    $sql = $sql . ' AND sqft >= :sqft ';
}
if (isset($bedrooms) ) {
    $sql = $sql . ' AND bedrooms >= :bedrooms ';
}


$stmt = $dbh->prepare($sql);


if (isset($price) ) {
    $stmt->bindParam(':price', $price);
}
if (isset($sqft) ) {
    $stmt->bindParam(':price', $price);
}
if (isset($bedrooms) ) {
    $stmt->bindParam(':bedrooms', $bedrooms);
}


$stmt->execute();
$result_set = $stmt->fetchAll(PDO::FETCH_ASSOC);

What I notice is the redundant multiple IF statements I have.

Question: is there any way to clean up my code so that I don't have these multiple IF statements for prepared statements?

Answer 1

This is very similar to a question a user asked me recently the forum for my book SQL Antipatterns. I gave him an answer similar to this:

$sql = "SELECT name, address, city FROM tableA JOIN tableB ON tableA.id = tableB.id";

$params = array();
$where = array();

if (isset($price) ) {
    $where[] = '(price = :price)';
    $params[':price'] = $price;
}
if (isset($sqft) ) {
    $where[] = '(sqft >= :sqft)';
    $params[':sqft'] = $sqft;
}
if (isset($bedrooms) ) {
    $where[] = '(bedrooms >= :bedrooms)';
    $params[':bedrooms'] = $bedrooms;
}

if ($where) {
  $sql .= ' WHERE ' . implode(' AND ', $where);
}

$stmt = $dbh->prepare($sql);

$stmt->execute($params);
$result_set = $stmt->fetchAll(PDO::FETCH_ASSOC);