CODEWITHSUNDEEP
phpmysqlsqlpdo
François M.
François M.

Reputation: 4278

Optional parameter in prepared query

Using MySQL and PDO, I have the following prepared statement : 

$stmt = $bdd->prepare('SELECT field FROM table WHERE category=:category;');

Then if I do the following, it works fine, I have all I want in $results :

$stmt->execute(array('category' => $someCategory));
$results = $stmt->fetchAll();

However, I also want to be able to execute the same statement, without the WHERE clause. Is it possible to execute the prepared statement and have it ignore the WHERE clause (for instance by passing no parameter in it) ?

Or do I have to make a new SELECT field FROM table; query ?

NB : $stmt->execute(); returns “No data supplied for parameters in prepared statement” and $stmt->execute(array('category' => '')); returns nothing.

Thanks

Upvotes: 0

Views: 1336

Answers (2)

Nerdwood
Nerdwood

Reputation: 4022

I think the if...else solution posed by @Oldskool is fine for this case, but here's another idea that might be helpful.

This allows you to have another parameters that toggles the filter (WHERE clause) on and off.

$stmt = $bdd->prepare('SELECT field FROM table WHERE (1=:filter AND category=:category) OR (0=:filter);');
$stmt->execute(array(
    'filter' => 1, //1 = on; 0 = off
    'category' => $someCategory));
$results = $stmt->fetchAll();

Upvotes: 4

Oldskool
Oldskool

Reputation: 34837

If you don't always have a category and want to run this query for both situations, a simple if/else construction would suffice:

if (!empty($someCategory)) {
    $stmt = $bdd->prepare('SELECT field FROM table WHERE category=:category;');
    $stmt->execute(array('category' => $someCategory));
} else {
    $stmt = $bdd->prepare('SELECT field FROM table;');
    $stmt->execute();
}

$results = $stmt->fetchAll();

Upvotes: 0

Related Questions