How to Fetch All Results from a MySQLi LIKE Query?

Retrieving Multiple Records from MySQLi LIKE Queries

MySQLi's LIKE queries can yield multiple results. However, the standard fetch() method only retrieves the first row. This guide outlines several ways to fetch all matching records.

Method 1: get_result() and fetch_all()

This is the preferred method. get_result() fetches the entire result set, and fetch_all() efficiently converts it into 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: Prepared Statements with fetch() and Looping

This approach utilizes prepared statements with bind_result() and iterates using fetch() until no more rows are available:

$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()) {
  // Process each row ($id, $username)
}

Method 3: execute_query() (PHP 8.2 and later)

For PHP 8.2 and above, execute_query() provides a concise alternative:

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

Helpful Resources:

• mysqli_get_result() documentation
• mysqli_fetch_all() documentation

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