php mysqli prepared statement LIKE

问题:

How can I with mysqli make a query with LIKE and get all results?

This is my code but it dosn\'t work:

$param = \"%{$_POST[\'user\']}%\";
$stmt = $db->prepare(\"SELECT id,Username FROM users WHERE Username LIKE ?\");
$stmt->bind_param(\"s\", $param);
$stmt->execute();
$stmt->bind_result($id,$username);
$stmt->fetch();

This code it doesn\'t seem to work. I have searched it a lot. Also it may return more than 1 row. So how can I get all the results even if it returns more than 1 row?

回答1:

Here\'s how you properly fetch the result

$param = \"%{$_POST[\'user\']}%\";
$stmt = $db->prepare(\"SELECT id,Username FROM users WHERE Username LIKE ?\");
$stmt->bind_param(\"s\", $param);
$stmt->execute();
$stmt->bind_result($id,$username);

while ($stmt->fetch()) {
  echo \"Id: {$id}, Username: {$username}\";
}

or you can also do:

$param = \"%{$_POST[\'user\']}%\";
$stmt = $db->prepare(\"SELECT id,Username FROM users WHERE Username LIKE ?\");
$stmt->bind_param(\"s\", $param);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
  foreach ($row as $r) {
    print \"$r \";
  }
  print \"\\n\";
}

I hope you realise I got the answer directly from the manual here and here, which is where you should\'ve gone first.

回答2:

---

Updated

From comments it is found that LIKE wildcard characters (_and %) are not escaped by default on Paramaterised queries and so can cause unexpected results.

Therefore when using \"LIKE\" statements, use this \'negative lookahead\' Regex to ensure these characters are escaped :

$param = preg_replace(\'/(?<!\\\\\\)([%_])/\', \'\\\\\\$1\',$param);

---

As an alternative to the given answer above you can also use the MySQL CONCAT function thus:

$stmt = $db->prepare(\"SELECT id,Username FROM users WHERE Username LIKE CONCAT(\'%\',?,\'%\') \");
$stmt->bind_param(\"s\", $param);
$stmt->execute();

Which means you do not need to edit your $param value but does make for slightly longer queries.