How to Efficiently Fetch All Results from a MySQL LIKE Query?

MySQL Query with LIKE and Multiple Results

In MySQL, using the LIKE operator in a query can often return multiple results. To retrieve all the matching results, you need to use the proper fetching methods.

Fetching All Results

To fetch all the results from a query, you can utilize the get_result() method. This method retrieves the complete result set as a MySQLi_Result object. You can then use the fetch_all() method to obtain an array of all the row data, associated by field names if you've used the MYSQLI_ASSOC constant.

Example Code Using get_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);

Example Code Using PHP 8.2 execute_query():

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

Iterative Fetching

If you prefer to fetch the results iteratively, you can use the fetch() method. However, you need to handle the iteration yourself, as there's no built-in way of looping through all the results.

Example Code for Iterative Fetching with bind_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}";
}

Remember to always refer to the MySQLi documentation for the most up-to-date information on fetching results.

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