mysql其中column =所有可能的值

Question

Is there any way to do something like this? Give the $variable a default value to make mysql return all the rows?

SELECT * FROM table WHERE column = ?;

bind_param('i', $variable);

It's not as stupid as it sounds... I need to filter content based on user input, so If user does not fill the input/select the value will be ALL (or the correct answer). I wouldn't like to print php variables inside the prepare() or make the default variable all the possible values. What is the correct way to do this?

I'm actually doing this:

if ($stmt = $mysqli->prepare(' SELECT t1.id, t2.*
                               FROM ' . $table . ' t1
                               INNER JOIN property t2 ON t2.id = t1.id
                               WHERE t2.use = ?
                               ' . $city_cond
                                 . $zone_cond . '
                               LIMIT ?, ?'))

But I wouldn't like to print inside the prepare() so I ask if is there any way to do it with the bind_param() and declaring a default value.

Answer 1

I use something like this for similar problems:

$sql = 'SELECT * FROM table WHERE cond1 = ?';
$sqlparams = array('cond1');
if (!empty($column) && $column != 'ALL') {
    $sql .= ' AND column = ?';
    $sqlparams[] = $column;
}
// add more conditions, ORDER BY, LIMIT etc.
$stmt = $dbh->prepare($sql);
$stmt->execute($sqlparams);

Differences with mysqli (untested):

-    $sqlparams[] = $column;
+    $sqlparams[] = array('s', $column);
---
-$stmt->execute($sqlparams);
+foreach ($sqlparams as $sqlparam) {
+    $stmt->bind_param($sqlparam[0], $sqlparam[1]);
+}
+$stmt->execute();

Maybe this can be done with named parameters and always bind, needed or not.

Another approach:

$sql = 'SELECT * FROM table WHERE cond1 = ? AND (1 = ? OR column = ?)';
$stmt = $dbh->prepare($sql);
$stmt->bind_param('i', (empty($column) ? 1 : 0));
$stmt->bind_param('s', $column);
$stmt->execute();