Preparing a query with mysqli_stmt_prepare binding a logical criteria after WHERE

Question

I need to dynamically launch queries based on filters which change over time. Every time the filter is in the string variable $filter. Am I allowed to bind it after WHERE like in the following code? It doesn't seem to work. The resulting query I expect is SELECT NAME FROM PERSONA WHERE GENDER='F'

$filter = "GENDER='F'";
$stmt = mysqli_stmt_init($connection);
mysqli_stmt_prepare($stmt, "SELECT NAME FROM PERSONA WHERE ?");
mysqli_stmt_bind_param($stmt, 's', $filter);

That's an odd way to bind. I would expect to see `WHERE GENDER = ?`, and you would bind `F` to that parameter... — random_user_name, Dec 24 '16 at 18:25
Possible duplicate of [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) — Funk Forty Niner, Dec 24 '16 at 18:36
The idea is that $filter may change and does not always refer to GENDER: it may be CITY='NY' and so on. — Mariano Tomatis, Dec 24 '16 at 19:22

score 0 · Answer 1 · answered Dec 24 '16 at 18:26

You can only bind values, not syntactic elements. So either keep the gender condition and just bind the F value:

mysqli_stmt_prepare($stmt, "SELECT NAME FROM PERSONA WHERE GENDER=?");
mysqli_stmt_bind_param($stmt, 's', 'F');

Or use string manipulation to construct the query:

mysqli_stmt_prepare($stmt, "SELECT NAME FROM PERSONA WHERE $filter");

score -1 · Answer 2 · answered Dec 25 '16 at 07:52

The other answer is obviously wrong. As the first suggestion doesn't answer the question at all, and the second one would work only for gender. While even for a CITY would be just impossible.

So you will have to create your query dynamically.
With mysqli it's too toilsome so I'll show how to do that with PDO:

$sql = "SELECT NAME FROM PERSONA WHERE 1";
$filters = [];
$params = []
if ($gender)
{
    $filters[] = " AND GENDER=:gender";
    $params['gender'] = $gender;
}
$sql .= implode("", $filters);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$data = $stmt->fetchAll();

Preparing a query with mysqli_stmt_prepare binding a logical criteria after WHERE

2 Answers2