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

Question

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?

Answer 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();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

Note that starting from PHP 8.2 (with a fallback replacement for 8.0+) a shorter code can be used

$sql = "SELECT id, username FROM users WHERE username LIKE ?";
$result = $db->execute_query($sql, ["%{$_POST['user']}%"]);
$data = $result->fetch_all(MYSQLI_ASSOC);

or, if you prefer the old fetch and bind_result syntax, 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();

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

I got the answer directly from the manual here and here.

Answer 2

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();

PDO named placeholder version:

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

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

---