基于PHP与MySQLi实现分类及子分类树形结构展示

Hey there! Let's walk through exactly how to build that expandable category-subcategory tree using your existing database tables. I'll cover both the backend data handling and frontend interactive part, since you need both pieces to make this work smoothly.

1. First: Structure Your Data (Backend Example) #

First off, you need to fetch your categories and their linked subcategories in a way that's easy for the frontend to work with. I'll use PHP + MySQL as a common example, but the logic translates to other languages (Python, Node.js, etc.) too.

Step 1: Fetch & Map Categories + Subcategories #

Instead of querying the database multiple times per category, we'll pull all data once and map subcategories to their parent categories:

// Assume you already have a database connection set up
$conn = mysqli_connect("localhost", "username", "password", "your_db");

// 1. Grab all main categories first
$categories = [];
$catQuery = "SELECT cat_id, catName FROM category ORDER BY catName ASC";
$catResult = mysqli_query($conn, $catQuery);

while ($row = mysqli_fetch_assoc($catResult)) {
    // Initialize each category with an empty subcategory array
    $categories[$row['cat_id']] = [
        'name' => $row['catName'],
        'subcategories' => []
    ];
}

// 2. Fetch all subcategories and map them to their parent
$subCatQuery = "SELECT id, cat_id, subCatName FROM subcategory ORDER BY subCatName ASC";
$subCatResult = mysqli_query($conn, $subCatQuery);

while ($row = mysqli_fetch_assoc($subCatResult)) {
    // Only add if the parent category exists (avoids orphaned subcats)
    if (isset($categories[$row['cat_id']])) {
        $categories[$row['cat_id']]['subcategories'][] = [
            'id' => $row['id'],
            'name' => $row['subCatName']
        ];
    }
}

// Convert to a flat array (removes cat_id keys) for easier frontend use
$treeData = array_values($categories);

// If you're using AJAX, send this as JSON
header('Content-Type: application/json');
echo json_encode($treeData);

2. Frontend: Build the Expandable Tree #

Now let's turn that structured data into an interactive tree with HTML, CSS, and JavaScript.

HTML Container #

Start with a simple container where the tree will live:

<div id="category-tree"></div>

CSS Styling #

Add some basic styling to make the tree look clean and indicate clickable items:

/* Main category container */
.category-container {
    margin: 5px 0;
}

/* Clickable category header */
.category-item {
    cursor: pointer;
    padding: 8px 12px;
    background-color: #f0f0f0;
    border-radius: 4px;
    font-weight: 500;
}

/* Subcategory list (hidden by default) */
.subcategory-list {
    margin-left: 25px;
    margin-top: 4px;
    display: none;
}

/* Individual subcategory item */
.subcategory-item {
    padding: 6px 10px;
    background-color: #ffffff;
    border-radius: 3px;
    margin: 2px 0;
    border: 1px solid #eee;
}

/* Expand state: show subcategories */
.category-container.expanded .subcategory-list {
    display: block;
}

/* Add +/- indicators to category headers */
.category-item::before {
    content: "+ ";
    font-size: 14px;
    margin-right: 6px;
}

.category-container.expanded .category-item::before {
    content: "- ";
}

JavaScript for Interactivity #

Use JavaScript to render the tree and handle expand/collapse clicks. If you're using AJAX, fetch the data from your backend endpoint:

// Fetch the structured category data from your backend
fetch('your-backend-endpoint.php')
    .then(response => response.json())
    .then(treeData => {
        const treeContainer = document.getElementById('category-tree');

        // Loop through each main category
        treeData.forEach(category => {
            // Create the main category container
            const catContainer = document.createElement('div');
            catContainer.className = 'category-container';

            // Create the clickable category header
            const catHeader = document.createElement('div');
            catHeader.className = 'category-item';
            catHeader.textContent = category.name;

            // Create subcategory list
            const subList = document.createElement('div');
            subList.className = 'subcategory-list';

            // Add each subcategory to the list
            category.subcategories.forEach(subCat => {
                const subItem = document.createElement('div');
                subItem.className = 'subcategory-item';
                subItem.textContent = subCat.name;
                subList.appendChild(subItem);
            });

            // Add click event to toggle expand/collapse
            catHeader.addEventListener('click', () => {
                catContainer.classList.toggle('expanded');
            });

            // Assemble all elements and add to the tree
            catContainer.appendChild(catHeader);
            catContainer.appendChild(subList);
            treeContainer.appendChild(catContainer);
        });
    })
    .catch(error => console.error('Error loading category tree:', error));

3. Alternative: Server-Side Rendering (No AJAX) #

If you prefer to render the HTML directly on the server (no JavaScript fetch), you can output the tree structure directly from PHP:

<div id="category-tree">
<?php foreach ($treeData as $category): ?>
    <div class="category-container">
        <div class="category-item"><?php echo htmlspecialchars($category['name']); ?></div>
        <div class="subcategory-list">
            <?php foreach ($category['subcategories'] as $subCat): ?>
                <div class="subcategory-item"><?php echo htmlspecialchars($subCat['name']); ?></div>
            <?php endforeach; ?>
        </div>
    </div>
<?php endforeach; ?>
</div>

<!-- Include the same CSS and add this small JS snippet for interactivity -->
<script>
document.querySelectorAll('.category-item').forEach(item => {
    item.addEventListener('click', () => {
        item.parentElement.classList.toggle('expanded');
    });
});
</script>

4. Important Tips to Avoid Issues #

• Sanitize Data: Always use htmlspecialchars() (PHP) or frontend text escaping to prevent XSS attacks, especially if category/subcategory names are user-generated.
• Handle Empty Subcategories: If a category has no subcategories, you can hide the +/- indicator or add a "No subcategories" message to avoid confusion.
• Lazy Loading (For Large Datasets): If you have hundreds of categories, consider loading subcategories only when the user clicks a category (instead of all at once). Just pass the cat_id to your backend and fetch only that category's subcats.

内容的提问来源于stack exchange，提问作者Nikita Agrawal