English
English
In modern web development, the search function is a very important component. To improve the user experience, many websites display some search suggestions below the search box to help users find the information they want more quickly. This article will use a simple example to show how to implement a backend search suggestion interface function using PHP and mysqli_result function.
First, we need to connect to the database. The following is the code to connect using PHP's mysqli extension:
<?php
$host = "localhost";
$username = "root";
$password = "yourpassword";
$database = "yourdatabase";
$conn = new mysqli($host, $username, $password, $database);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
This code connects to the database server and selects a database to perform subsequent queries.
We will create a simple search suggestions interface that allows users to enter keywords and the backend returns a list of suggestions that meet the criteria. Here is the code that implements this function:
<?php
// Get keywords entered by the user
$keyword = isset($_GET['query']) ? $_GET['query'] : '';
// prevent SQL injection
$keyword = $conn->real_escape_string($keyword);
// Define a query SQL Statement
$sql = "SELECT * FROM products WHERE name LIKE '%$keyword%' LIMIT 5";
// Execute a query
$result = $conn->query($sql);
// Determine whether the query is successful
if ($result->num_rows > 0) {
$suggestions = [];
while ($row = $result->fetch_assoc()) {
$suggestions[] = $row['name'];
}
echo json_encode($suggestions); // Return to the search suggestions list
} else {
echo json_encode([]); // If there is no matching result，Return an empty array
}
$conn->close();
?>
In the above code, first get the query string entered by the user through $_GET['query'] , and then use the real_escape_string() method to avoid SQL injection attacks. Next, we use SQL statements to query records in the product table in the database that matches the keywords entered by the user, and return up to 5 suggestions.
The mysqli_result object takes out the results of each row through the fetch_assoc() method and saves it into an array $suggestions . Finally, convert this array into JSON format and output it. The front-end can receive and display search suggestions through AJAX.
In order for this backend interface to work with the frontend page, we need to use JavaScript on the frontend to send requests to the backend through AJAX and receive the returned data. Here is a simple front-end code example:
<!DOCTYPE html>
<html lang="zh">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Search for suggestions examples</title>
<style>
#suggestions {
border: 1px solid #ccc;
max-height: 200px;
overflow-y: auto;
}
.suggestion-item {
padding: 8px;
cursor: pointer;
}
.suggestion-item:hover {
background-color: #f0f0f0;
}
</style>
</head>
<body>
<input type="text" id="search" placeholder="Enter search keywords..." oninput="getSuggestions()">
<div id="suggestions"></div>
<script>
function getSuggestions() {
var query = document.getElementById('search').value;
if (query.length > 0) {
var xhr = new XMLHttpRequest();
xhr.open('GET', 'search_suggestions.php?query=' + query, true);
xhr.onload = function() {
if (xhr.status == 200) {
var suggestions = JSON.parse(xhr.responseText);
displaySuggestions(suggestions);
}
}
xhr.send();
} else {
document.getElementById('suggestions').innerHTML = '';
}
}
function displaySuggestions(suggestions) {
var suggestionsDiv = document.getElementById('suggestions');
suggestionsDiv.innerHTML = '';
suggestions.forEach(function(item) {
var div = document.createElement('div');
div.classList.add('suggestion-item');
div.textContent = item;
suggestionsDiv.appendChild(div);
});
}
</script>
</body>
</html>
In this front-end code, we use the oninput event in the input box. When the user enters characters, the getSuggestions() function will be called, and the request is sent to search_suggestions.php through AJAX and the search suggestions are obtained. The returned suggestions will be displayed on the page through the displaySuggestions() function.
Through PHP and MySQL, combined with the mysqli_result function, we successfully implemented a simple backend search suggestion interface function. When a user enters keywords, the backend will return relevant suggestions based on the data in the database, while the frontend will display these suggestions dynamically through AJAX. This example shows how to use mysqli_result to efficiently get data from the database and return it to the front-end.
