How to Use mysqli::stmt_init for Efficient Keyword Fuzzy Search (LIKE Query)

When developing PHP applications, database queries are one of the most common operations. Especially when dealing with large datasets, improving query performance becomes a key focus. In PHP, using the mysqli extension to execute database queries is quite common, and mysqli::stmt_init is a great tool that helps us execute prepared statements, improving both query performance and security.

This article will explain how to use mysqli::stmt_init to achieve efficient keyword fuzzy search, specifically how to use the LIKE keyword in SQL queries.

1. Prepared Statements and mysqli::stmt_init

In PHP, mysqli provides the mysqli::stmt_init method to initialize a prepared statement. This means that the SQL query will be precompiled, and then executed through parameter binding, which helps avoid SQL injection risks. Prepared statements not only provide security but also offer better performance when executing the same query multiple times.

Syntax

$stmt = $mysqli->stmt_init();

2. Why Use LIKE for Fuzzy Queries

LIKE is commonly used in SQL for performing fuzzy searches. It is typically used to find strings that match a particular pattern. In fuzzy queries, % represents any number of characters (including zero characters), and _ represents a single character.

For example:

SELECT * FROM users WHERE username LIKE '%admin%';

This query will return all records where the username contains admin.

3. Using mysqli::stmt_init and LIKE for Efficient Fuzzy Search

Next, we will show how to use mysqli::stmt_init to implement an efficient keyword fuzzy search. Suppose we need to find users in the database where the username contains a specific keyword.

Step 1: Initialize Database Connection

First, we need to connect to the database:

$mysqli = new mysqli("localhost", "username", "password", "database");
<p>// Check connection<br>
if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}<br>

Step 2: Initialize Prepared Statement

We use mysqli::stmt_init to initialize a prepared statement object:

$stmt = $mysqli->stmt_init();

Step 3: Create Fuzzy Query

Next, we use LIKE for the fuzzy search. We use ? as a placeholder for parameters, to avoid SQL injection:

$sql = "SELECT * FROM users WHERE username LIKE ?";

Step 4: Bind Parameters

We need to bind the user input keyword to the ? in the SQL query, and add % for the LIKE query to indicate fuzzy matching:

$searchKeyword = "%$keyword%";  // User input keyword, surrounded by percentage signs
$stmt->prepare($sql);
$stmt->bind_param("s", $searchKeyword);  // "s" means binding a string parameter

Step 5: Execute Query

Execute the query and retrieve the results:

$stmt->execute();
$result = $stmt->get_result();

Step 6: Process Results

Use get_result() to fetch the query results and process them, such as outputting the results to the browser:

while ($row = $result->fetch_assoc()) {
    echo "User ID: " . $row['id'] . " - Username: " . $row['username'] . "<br>";
}

Step 7: Close Connection

After the query is complete, don’t forget to close the database connection:

$stmt->close();
$mysqli->close();

4. Conclusion

By using mysqli::stmt_init and prepared statements, we can effectively prevent SQL injection and improve query performance. Particularly when using LIKE for fuzzy queries, prepared statements help separate the query from the user input, enhancing both security and efficiency.

Moreover, it’s worth noting that while LIKE queries are very useful in certain scenarios, fuzzy searches can lead to performance issues when dealing with large datasets. Consider using database indexing or other optimization techniques to further improve query performance.