php mysqli prepared statement LIKE

Question

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?

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