How to use loops to query subcategories and optimize performance in PHP

PHP is a server-side scripting language widely used in web development. Due to its flexibility and ease of use, it is widely used in the development of various websites and web applications. . In actual development work, we often encounter situations where we need to query multi-level classified data in the database and perform loop processing. This article will introduce how to use loops to query subcategories and optimize performance, and provide specific PHP code examples.

1. Data table structure design

Before you start writing PHP code, you first need to design the structure of the database table. Normally, multi-level classification data can be stored using the following table structure:

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    INDEX parent_id_index (parent_id),
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

The above table structure contains the id, name and id of the parent category. The parent and child are established through parent_id and the id in the categories table. relation.

2. Use recursive query for subcategories

In PHP, you can query all subcategories recursively. The following is a simple example:

function getSubcategories($parent_id, $depth) {
    $categories = [];
    
    $sql = "SELECT * FROM categories WHERE parent_id = $parent_id";
    $result = mysqli_query($conn, $sql);
    
    while ($row = mysqli_fetch_assoc($result)) {
        $categories[] = $row;
        
        if ($depth > 0) {
            $subcategories = getSubcategories($row['id'], $depth - 1);
            $categories = array_merge($categories, $subcategories);
        }
    }
    
    return $categories;
}

// 调用函数查询所有子分类
$subcategories = getSubcategories($parent_id, $depth);

In the above code, the getSubcategories function receives the id and depth parameters of the parent category, recursively queries all subcategories, and stores them in the $categories array. In each recursive call, it is judged whether the depth parameter is greater than 0, and if so, continue to query the subcategory downwards. Finally, an array containing all subcategories is returned.

3. Optimize performance

When processing multi-level classification data, if the classification level is deep, using recursive queries may cause performance problems. In order to optimize performance, you can consider using a circular query plus caching method. The following is an example of using loop query and caching:

function getSubcategoriesWithCache($parent_id) {
    $categories = [];
    $cache = [];
    
    $queue = [$parent_id];
    $depth = 0;
    
    while (!empty($queue)) {
        $current_id = array_shift($queue);
        
        if (!isset($cache[$current_id])) {
            $sql = "SELECT * FROM categories WHERE parent_id = $current_id";
            $result = mysqli_query($conn, $sql);
            
            while ($row = mysqli_fetch_assoc($result)) {
                $categories[] = $row;
                $cache[$row['id']] = true;
                $queue[] = $row['id'];
            }
        }
        
        if ($depth > 0) {
            $depth--;
        } else {
            break;
        }
    }
    
    return $categories;
}

// 调用函数查询所有子分类
$subcategories = getSubcategoriesWithCache($parent_id);

In the above code, the getSubcategoriesWithCache function uses loop query and combines the caching mechanism to reduce the number of database queries. Use an array $cache to record the categories that have been queried to avoid repeated queries. A queue $queue is used to store the category ID to be queried, and the subcategories are queried layer by layer by looping through the queue.

Conclusion

Through the method introduced in this article, multi-level classification data can be efficiently processed in PHP and performance optimized. Using recursive query can easily and quickly obtain all subcategories, while using circular query plus caching can reduce the number of database accesses and improve query efficiency. In actual projects, depending on the amount of classified data and the depth of the hierarchy, choose an appropriate method to process multi-level classified data to improve system performance and user experience.

The above is the detailed content of How to use loops to query subcategories and optimize performance in PHP. For more information, please follow other related articles on the PHP Chinese website!