PHP实现电商后台商品录入页面的多表关联与下拉联动需求

Hey there! Let's walk through how to build that category association logic for your e-commerce admin product entry page—this is a super common requirement, so I'll break it down step by step with practical code examples.

Right now you have separate man and female tables for subcategories. While that works, merging them into a single sub_categories table with a category_id foreign key would make your schema way more scalable (think if you add a "unisex" category later!). Here's what that optimized table would look like:

CREATE TABLE sub_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES category(id)
);

You’d then migrate your existing man/female data into this table, setting category_id to the ID of "male" or "female" from the category table respectively.

If you want to stick with your current separate tables, no problem—I’ll cover that flow too.

First, set up your two nested dropdowns in HTML. We’ll populate them dynamically and add a listener to sync the subcategory options when the main category changes:

<!-- Main Category Dropdown -->
<select id="mainCategory">
  <option value="">Select Main Category</option>
  <!-- Options load from category table on page load -->
</select>

<!-- Sub Category Dropdown (disabled until main category is selected) -->
<select id="subCategory" disabled>
  <option value="">Select Sub Category</option>
</select>

Add this vanilla JavaScript to handle the dynamic population and sync:

// Populate main category dropdown when page loads
document.addEventListener('DOMContentLoaded', function() {
  fetch('/api/get-main-categories')
    .then(res => res.json())
    .then(data => {
      const mainCatSelect = document.getElementById('mainCategory');
      data.forEach(category => {
        const option = document.createElement('option');
        option.value = category.id;
        option.textContent = category.name; // Assumes category table has a 'name' field for 'male'/'female'
        mainCatSelect.appendChild(option);
      });
    });

  // Sync subcategories when main category changes
  document.getElementById('mainCategory').addEventListener('change', function() {
    const categoryId = this.value;
    const subCatSelect = document.getElementById('subCategory');
    
    // Reset and toggle subcategory dropdown state
    subCatSelect.disabled = !categoryId;
    subCatSelect.innerHTML = '<option value="">Select Sub Category</option>';

    if (categoryId) {
      fetch(`/api/get-sub-categories?categoryId=${categoryId}`)
        .then(res => res.json())
        .then(subCategories => {
          subCategories.forEach(subCat => {
            const option = document.createElement('option');
            option.value = subCat.id;
            option.textContent = subCat.name;
            subCatSelect.appendChild(option);
          });
        });
    }
  });
});

Here’s how to build the backend endpoints to fetch category data. I’ll use PHP as an example, but you can adapt this to Node.js, Python, or whatever stack you’re using.

Endpoint 1: Get Main Categories (/api/get-main-categories.php) #

<?php
// Connect to your database (replace with your credentials)
$conn = new mysqli('localhost', 'db_user', 'db_pass', 'ecommerce_db');

// Fetch all main categories
$result = $conn->query("SELECT id, name FROM category");
$categories = [];
while ($row = $result->fetch_assoc()) {
  $categories[] = $row;
}

echo json_encode($categories);
$conn->close();
?>

Endpoint 2: Get Sub Categories (/api/get-sub-categories.php) #

For your current separate man/female tables:

<?php
$conn = new mysqli('localhost', 'db_user', 'db_pass', 'ecommerce_db');
$categoryId = $_GET['categoryId'];

// First get the main category name to know which sub table to query
$catResult = $conn->query("SELECT name FROM category WHERE id = $categoryId");
$category = $catResult->fetch_assoc();
$subCategories = [];

if ($category['name'] === 'male') {
  $subResult = $conn->query("SELECT id, name FROM man");
} elseif ($category['name'] === 'female') {
  $subResult = $conn->query("SELECT id, name FROM female");
}

while ($row = $subResult->fetch_assoc()) {
  $subCategories[] = $row;
}

echo json_encode($subCategories);
$conn->close();
?>

For the optimized sub_categories table:

<?php
$conn = new mysqli('localhost', 'db_user', 'db_pass', 'ecommerce_db');
$categoryId = $_GET['categoryId'];

$result = $conn->query("SELECT id, name FROM sub_categories WHERE category_id = $categoryId");
$subCategories = [];
while ($row = $result->fetch_assoc()) {
  $subCategories[] = $row;
}

echo json_encode($subCategories);
$conn->close();
?>

When the user submits the product form, include both the selected category_id and sub_category_id (or man_id/female_id if using separate tables) in your INSERT query. Here’s an example for the optimized schema:

-- Insert product with associated categories (adjust fields to match your product table)
INSERT INTO product (name, price, description, category_id, sub_category_id)
VALUES ('Premium Grooming Kit', 49.99, 'Complete men''s grooming set', 1, 3);
-- Where 1 = ID of 'male' in category table, 3 = ID of 'grooming kit' in sub_categories table

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