English
English
In PHP, the mysqli extension library provides rich functionality for interaction with MySQL databases. mysqli_result is a common result set object that allows you to obtain query results in a convenient way, filter and sort. This article will use an example to introduce how to use the mysqli_result function to implement server-side data filtering and sorting functions.
When building PHP web applications, in many cases, large amounts of data need to be processed. Filtering and sorting this data on the client may cause unnecessary waiting time for the user. To improve efficiency, the filtering and sorting logic can be placed on the server side for execution. This article will explain how to achieve this goal through mysqli_result in PHP.
First, we need to establish a connection to the MySQL database. You can use the mysqli_connect function to connect to the database:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";
// Create a connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
In this example, we connect to a database named test_db . If the database connection fails, we will output an error message.
Suppose we have a data table called products that contains fields id , name , price and created_at . Next, we will write an SQL query to get the data and use the mysqli_query function to execute the query and return the result.
<?php
$sql = "SELECT * FROM products";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Price: " . $row["price"]. "<br>";
}
} else {
echo "No data";
}
?>
This query returns a list of all products and outputs the results line by line.
In order to improve the user experience, we can implement the filtering and sorting functions of data. Suppose we want to filter and sort products by price and name.
We can filter data by receiving URL parameters. For example, users want to filter out products with prices greater than 50. It can be implemented in the following ways:
<?php
$price_filter = isset($_GET['price']) ? $_GET['price'] : 0;
$sql = "SELECT * FROM products WHERE price > $price_filter";
$result = mysqli_query($conn, $sql);
?>
Additionally, users may also need to sort the data by price or name. We can use the ORDER BY clause to achieve this:
<?php
$sort_by = isset($_GET['sort']) ? $_GET['sort'] : 'price';
$order = isset($_GET['order']) ? $_GET['order'] : 'ASC';
$sql = "SELECT * FROM products ORDER BY $sort_by $order";
$result = mysqli_query($conn, $sql);
?>
In this example, the user can specify the sorted fields ( sort ) and the sort order ( order , which can be ASC or DESC ) through the URL parameter. For example, sort=price&order=DESC will be sorted in descending order of prices.
In order to implement the filtering and sorting functions at the same time, we can combine two functions:
<?php
$price_filter = isset($_GET['price']) ? $_GET['price'] : 0;
$sort_by = isset($_GET['sort']) ? $_GET['sort'] : 'price';
$order = isset($_GET['order']) ? $_GET['order'] : 'ASC';
$sql = "SELECT * FROM products WHERE price > $price_filter ORDER BY $sort_by $order";
$result = mysqli_query($conn, $sql);
?>
In this way, users can choose between filtering conditions (price is greater than a certain value) and sorting methods (sorted by price or name, ascending or descending).
Here is a complete PHP sample code that combines filtering and sorting functions:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$price_filter = isset($_GET['price']) ? $_GET['price'] : 0;
$sort_by = isset($_GET['sort']) ? $_GET['sort'] : 'price';
$order = isset($_GET['order']) ? $_GET['order'] : 'ASC';
$sql = "SELECT * FROM products WHERE price > $price_filter ORDER BY $sort_by $order";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Price: " . $row["price"]. "<br>";
}
} else {
echo "No data";
}
mysqli_close($conn);
?>
In this example, the user can pass filtering and sorting parameters through the URL, such as:
http://m66.net/products.php?price=50&sort=name&order=ASC : Filter products with prices greater than 50 and arrange them in ascending order of names.
http://m66.net/products.php?price=100&sort=price&order=DESC : Filter products with prices greater than 100 and arrange them in descending price order.
This article introduces how to implement server-side data filtering and sorting functions in PHP through the mysqli_result function. By combining SQL's WHERE and ORDER BY clauses, we can efficiently process large amounts of data and reduce the burden on clients. In practical applications, you can adjust filtering conditions and sorting rules according to your needs to provide a better user experience.
