How to Retrieve Multiple LIKE Query Results Using MySQLi?

MySQLi and Multiple LIKE Query Results: A Comprehensive Guide

Using MySQLi's LIKE operator often presents challenges when retrieving multiple matching rows. This guide demonstrates the correct techniques to avoid such issues.

Optimized Code for Multiple Row Retrieval:

The following code snippet efficiently retrieves all matching records from your users table and stores them in the $data array:

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

The key here is utilizing get_result(). This method returns a MySQLi result object, enabling seamless access and processing of the entire result set.

Modern Approach (PHP 8.2 and above):

For PHP 8.2 and later versions, a more concise method exists:

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

This leverages the execute_query() method, simplifying the process considerably.

Traditional Fetch and bind_result Method:

While less efficient, the traditional approach using fetch() and bind_result() remains an option:

$stmt->bind_result($id, $username);
while ($stmt->fetch()) {
  echo "Id: {$id}, Username: {$username}";
}

Note that this requires a while loop to iterate through each row individually.

Essential Resources:

For detailed information and further exploration, refer to these resources:

• MySQLi LIKE operator: https://www.php.cn/link/c2aa4a54d4c5f5728e121f37b40472b9
• get_result() method: https://www.php.cn/link/74707d064c4eeda12c3bf2f1807a7198
• fetch_all() method: https://www.php.cn/link/2ff87ef2c8e0ec43f54888b94b9d5d05

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