How can I with mysqli make a query with LIKE and get all results?
PhpSqlMysqliSql LikePhp Problem Overview
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?
Php Solutions
Solution 1 - Php
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_assoc()) {
echo "Id: {$row['id']}, Username: {$row['username']}";
}
I hope you realise I got the answer directly from the manual here and here, which is where you should've gone first.
Solution 2 - Php
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.
Solution 3 - Php
The answer is obviously outdated, you don’t even need to use bind_param.
$querySql = "SELECT * FROM table WHERE columnName LIKE :likeKeyWord";
$likeKeyWord = "%". $keyWord ."%";
$pdoStm = $this -> db -> prepare($querySql);
$pdoStm -> execute([':likeKeyWord' => $likeKeyWord]);
return $pdoStm -> fetchAll();