How can I with mysqli make a query with LIKE and get all results?

Prepared Statements for Secure Queries

Shoring up your defenses is important! Prepared statements help to prevent SQL injection. We use placeholders (?) in SQL statements:

$stmt = $mysqli->prepare("SELECT * FROM table WHERE column LIKE ?");
$searchWithWildcard = "%mySearchTerm%"; // Wild at heart!
$stmt->bind_param("s", $searchWithWildcard);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['column'];
}


Using % in the search term and not as a wildcard? Escape it:

$searchTerm = preg_replace('/(?<!\\\)%/', '\\%', $searchTerm); // Escapism at its finest!


Fetching Multiple Rows in One Go!

Why fetch one when you can fetch all? fetch_all(MYSQLI_ASSOC) fetches all results at once in an associative array. Bigger result sets, here we come:

if ($result = $stmt->get_result()) {
    $rows = $result->fetch_all(MYSQLI_ASSOC); // It's raining rows!
    foreach ($rows as $row) {
        echo $row['column'];
    }
}


PHP 8.2 has your back with the execute_query method for extra sleek code:

$rows = $mysqli->execute_query("SELECT * FROM table WHERE column LIKE ?", ["%searchTerm%"])->fetch_all(MYSQLI_ASSOC); // Executor, not executioner!


Combining Columns Efficiently with MySQL CONCAT

MySQL CONCAT() in your LIKE queries is a smart move when you need to search across combined columns:

$stmt = $mysqli->prepare("SELECT * FROM employees WHERE CONCAT(first_name, ' ', last_name) LIKE ?"); // Together, we unite!
$stmt->bind_param("s", $searchWithWildcard);


Visualising Query Patterns

Imagine a library catalog system (📚):

SELECT * FROM books WHERE title LIKE '%Harry%';


It's like telling the librarian:

"Can I see all the books with 'Harry' ANYWHERE in the title, please?"

Result? A cornucopia of 'Harry':

🔍📚: ["Harry Potter", "A History of Harry", "When Harry Met Sally"]

Behold! LIKE '%Harry%'. You'll find 'Harry' lurking in any nook and cranny of the title!

Escaping Wildcards with Regex Negative Lookahead

Search term with % or _ not as wildcards but literal characters? It's important to escape them. Let's invite regex negative lookahead to the party:

$searchTerm = preg_replace('/(?!\\)\%/', '\\%', $searchTerm); // "I ain't no wildcard!", exclaimed %
$searchTerm = preg_replace('/(?!\\)\_/', '\\_', $searchTerm); // _ doing an underscore of itself!


Binding Result to Variables

bind_result offers an alternative fetching method, binding results to variables:

$stmt->bind_result($column); // You're my bind mate!
while ($stmt->fetch()) {
    echo $column;
}
