Why Your LIKE Query Returns Only One Row and How to Fix It
You are running into a classic PHP MySQLi limitation regarding prepared statements, but the root cause lies specifically in how you are handling the result set. The code snippet you provided uses bind_result() followed immediately by fetch(). This combination is functionally equivalent to saying "give me exactly one row." If your query matches multiple usernames, your script will silently ignore every match after the first one.
To retrieve all results from a LIKE query, you must switch from bound variables to using get_result() or iterating through a result object. Prepared statements are designed for security and performance; they do not inherently restrict data retrieval. The restriction here is entirely procedural logic on your part.
Switching from Bound Variables to Result Objects
In the failing example, $stmt->bind_result($id, $username) sets up a pointer to receive data from the database into specific variables. Once that pointer is set and fetch() is called, the internal cursor moves past that single row. If you call fetch() again without resetting anything, it returns false because the statement has already been exhausted.
To get all rows, you need to detach your code from the strict variable-binding model. The modern approach for MySQLi involves converting the prepared statement into a result object using get_result(). This object provides an iterator-like interface that allows you to loop through every matching record without worrying about resetting variables manually.
Here is how the corrected logic looks:
$param = '%'.($_POST['user'] ?? '').'%'; // Use null coalescing for safety if POST is empty
$stmt = $db->prepare("SELECT id, Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
// Convert to a result object to iterate over all rows
$result = $stmt->get_result();
if ($result) {
while ($row = $result->fetch_assoc()) {
// Process every single match here
echo "Found ID: {$row['id']}, Username: {$row['Username']}<br>";
}
} else {
echo "No users found matching that pattern.";
}
$stmt->close();
Handling Multiple Matches Correctly
Your original concern was that the code "doesn't seem to work" when returning more than one row. The issue is that bind_result expects a specific number of columns and binds them sequentially. While you could technically call fetch() repeatedly after binding, it is error-prone because you must ensure the variable names match the order of your SQL columns perfectly every time.
By using get_result()->fetch_assoc(), you decouple the loop logic from the column binding. You retrieve the entire result set first, then iterate over it. This pattern scales better if you later need to fetch additional columns or pass the data into a view layer. It also makes debugging significantly easier; if one row fails to bind, the entire while loop breaks cleanly, whereas bound variables can sometimes mask errors until the next fetch call.
Security Considerations with LIKE Patterns
When constructing your $param variable, you are already correctly using a prepared statement to handle the % wildcards. This prevents SQL injection attacks even though the wildcard characters themselves are valid within the query string. However, be mindful of performance. A query like WHERE Username LIKE '%{input}%' forces the database engine to perform a full table scan because it cannot use an index on the username column when wildcards appear on both sides of the search term.
If your application handles high traffic or large user tables, consider normalizing input. If you only need to search for a substring at the start, change the query to LIKE '{input}%'. This allows the database optimizer to utilize indexes, drastically reducing query time compared to a full scan triggered by leading wildcards.
Why Not Just Use bind_result in a Loop?
You might wonder why we don't just stick with bind_result. Technically, you can fetch multiple rows after binding, but it requires explicit calls to fetch() inside a loop. While this works, the get_result() method is generally preferred in modern PHP development because it treats the query result as a collection rather than a stream of individual variables. It aligns better with how most developers expect to process lists of data, whether you are rendering them in a table or passing them to a serializer.
If you prefer sticking with older MySQLi methods, ensure your loop looks like this:
while ($stmt->fetch($stmt, MYSQLI_STORE_RESULT)) { // Note: Syntax varies slightly by version
// Logic here
}
However, relying on deprecated syntax is not recommended for new projects. The get_result() pattern is the standard for handling multi-row queries in PHP 7 and later environments.