How to Efficiently Retrieve All Results from a MySQLi LIKE Query with Multiple Matches?

Retrieving All Matches from a MySQLi LIKE Query

Working with MySQLi LIKE queries in PHP, especially when multiple results are expected, requires careful handling to ensure all matching records are retrieved. The following demonstrates efficient methods to achieve this.

A common mistake is only fetching the first result. To retrieve all matching rows, employ these improved techniques:

Method 1: Using fetch_all() (Recommended)

This method is concise and efficient, especially for PHP 8.2 and above. It fetches all results into an associative array:

<code class="language-php">$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); // Fetches all results</code>

For PHP 8.2 , the execute_query function simplifies this further:

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

Method 2: Iterative Approach with bind_result() and while loop

This approach iterates through each row individually, printing the id and username:

<code class="language-php">$stmt->bind_result($id, $username);
while ($stmt->fetch()) {
  echo "Id: {$id}, Username: {$username}<br>"; // Added <br> for better output
}</code>

This method is useful if you need to process each row individually, rather than storing them all in an array.

Both methods are based on official MySQLi documentation and provide robust solutions for handling multiple results from LIKE queries. The fetch_all() method is generally preferred for its simplicity and efficiency in retrieving all results at once.

The above is the detailed content of How to Efficiently Retrieve All Results from a MySQLi LIKE Query with Multiple Matches?. For more information, please follow other related articles on the PHP Chinese website!