How to Retrieve Multiple Rows with a LIKE Query Using mysqli?

MySQLi LIKE Queries: Retrieving Multiple Rows

The provided code uses bind_result and fetch, suitable only for single-row retrieval. To fetch multiple rows matching a LIKE query using MySQLi, consider these methods:

Method 1: Using get_result()

This method returns a result set object, allowing you to use fetch_all() to retrieve all rows as an associative 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);

Method 2: Using execute_query() (PHP 8.2 and later)

This streamlined approach executes the query and fetches all results simultaneously:

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

Method 3: Iterative fetch()

If you prefer the bind_result() and fetch() approach, use a loop to iterate through multiple rows:

$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}";
}

These alternatives efficiently retrieve all rows satisfying your LIKE condition. Choose the method that best suits your coding style and PHP version.

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