如何用PHP & MySQL处理电商平台带索引的商品分类校验问题？

Hey there, let’s work through how to fix that persistent category mislabeling headache with PHP and MySQL—while keeping those indexes you’ve set up working hard for you. Here’s a step-by-step approach:

1. Stop Bad Entries at the Source with Frontend Cascading Dropdowns #

The easiest way to cut down on invalid category selections is to force users to pick categories in a nested, logical order. Use cascading dropdowns where selecting a main category loads only valid subcategories, and selecting a subcategory loads only valid leaf categories.

Example Code: #

PHP to Fetch Main Categories:

<?php
// Connect to DB (use PDO or mysqli for safety)
$pdo = new PDO('mysql:host=localhost;dbname=your_ecommerce_db', 'db_user', 'db_pass');

// Fetch level 1 (main) categories
$mainCatsQuery = $pdo->query("SELECT id, name FROM categories_tbl WHERE level = 1");
$mainCategories = $mainCatsQuery->fetchAll(PDO::FETCH_ASSOC);
?>

HTML Dropdowns:

<select id="mainCategory" name="main_category" required>
  <option value="">Select Main Category</option>
  <?php foreach($mainCategories as $cat): ?>
    <option value="<?= $cat['id'] ?>"><?= htmlspecialchars($cat['name']) ?></option>
  <?php endforeach; ?>
</select>

<select id="subCategory" name="sub_category" disabled required>
  <option value="">Select Sub Category</option>
</select>

<select id="leafCategory" name="leaf_category" disabled required>
  <option value="">Select Leaf Category</option>
</select>

JS for Dynamic Loading (jQuery):

// Load subcategories when main category changes
$('#mainCategory').on('change', function() {
  const mainCatId = $(this).val();
  $('#subCategory').prop('disabled', !mainCatId).empty().append('<option value="">Select Sub Category</option>');
  $('#leafCategory').prop('disabled', true).empty().append('<option value="">Select Leaf Category</option>');

  if (mainCatId) {
    $.post('get_sub_cats.php', { main_cat_id: mainCatId }, function(data) {
      const subCats = JSON.parse(data);
      subCats.forEach(cat => {
        $('#subCategory').append(`<option value="${cat.id}">${cat.name}</option>`);
      });
    });
  }
});

// Load leaf categories when subcategory changes
$('#subCategory').on('change', function() {
  const subCatId = $(this).val();
  $('#leafCategory').prop('disabled', !subCatId).empty().append('<option value="">Select Leaf Category</option>');

  if (subCatId) {
    $.post('get_leaf_cats.php', { sub_cat_id: subCatId }, function(data) {
      const leafCats = JSON.parse(data);
      leafCats.forEach(cat => {
        $('#leafCategory').append(`<option value="${cat.id}">${cat.name}</option>`);
      });
    });
  }
});

Backend for Dynamic Category Fetching (get_sub_cats.php):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=your_ecommerce_db', 'db_user', 'db_pass');
$mainCatId = $_POST['main_cat_id'];

$stmt = $pdo->prepare("SELECT id, name FROM categories_tbl WHERE level = 2 AND parent_id = ?");
$stmt->execute([$mainCatId]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));
?>

2. Backend Validation to Catch Bypassed Entries #

Even if users bypass frontend checks (e.g., direct POST requests), validate the category hierarchy on the backend before saving the product.

Example Validation Code: #

<?php
$mainCatId = $_POST['main_category'];
$subCatId = $_POST['sub_category'];
$leafCatId = $_POST['leaf_category'];
$productName = $_POST['product_name'];

$isValid = true;

// Check main category is a valid level 1 category
$stmt = $pdo->prepare("SELECT id FROM categories_tbl WHERE id = ? AND level = 1");
$stmt->execute([$mainCatId]);
if (!$stmt->rowCount()) $isValid = false;

// Check subcategory is a valid child of the main category
$stmt = $pdo->prepare("SELECT id FROM categories_tbl WHERE id = ? AND level = 2 AND parent_id = ?");
$stmt->execute([$subCatId, $mainCatId]);
if (!$stmt->rowCount()) $isValid = false;

// Check leaf category is a valid child of the subcategory
$stmt = $pdo->prepare("SELECT id FROM categories_tbl WHERE id = ? AND level = 3 AND parent_id = ?");
$stmt->execute([$leafCatId, $subCatId]);
if (!$stmt->rowCount()) $isValid = false;

if ($isValid) {
  // Save product with auto-approved status (or keep is_reviewed=0 for final check)
  $stmt = $pdo->prepare("INSERT INTO products_tbl (main_category, sub_category, leaf_category, name, is_reviewed) VALUES (?, ?, ?, ?, 1)");
  $stmt->execute([$mainCatId, $subCatId, $leafCatId, $productName]);
} else {
  // Reject submission or mark for urgent review
  $stmt = $pdo->prepare("INSERT INTO products_tbl (main_category, sub_category, leaf_category, name, is_reviewed, review_notes) VALUES (?, ?, ?, ?, 0, 'Invalid category hierarchy')");
  $stmt->execute([$mainCatId, $subCatId, $leafCatId, $productName]);
  header('Location: submit_product.php?error=invalid_category');
  exit;
}
?>

3. Bulk Audit & Fix Existing Unreviewed Products #

For existing unapproved products, run a query to identify those with invalid category mappings, then build a backend interface to fix them efficiently.

MySQL Query to Find Invalid Products: #

SELECT 
  p.id,
  p.name AS product_name,
  p.main_category, COALESCE(main.name, 'INVALID: ' || p.main_category) AS main_cat,
  p.sub_category, COALESCE(sub.name, 'INVALID: ' || p.sub_category) AS sub_cat,
  p.leaf_category, COALESCE(leaf.name, 'INVALID: ' || p.leaf_category) AS leaf_cat
FROM products_tbl p
LEFT JOIN categories_tbl main 
  ON p.main_category = main.id AND main.level = 1
LEFT JOIN categories_tbl sub 
  ON p.sub_category = sub.id AND sub.level = 2 AND sub.parent_id = main.id
LEFT JOIN categories_tbl leaf 
  ON p.leaf_category = leaf.id AND leaf.level = 3 AND leaf.parent_id = sub.id
WHERE p.is_reviewed = 0
  AND (main.id IS NULL OR sub.id IS NULL OR leaf.id IS NULL);

PHP Backend Table for Fixes: #

<?php
$stmt = $pdo->query("<INSERT THE QUERY ABOVE HERE>");
$invalidProducts = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<h2>Products with Invalid Categories</h2>
<table border="1">
  <tr>
    <th>Product ID</th>
    <th>Product Name</th>
    <th>Current Main Category</th>
    <th>Current Sub Category</th>
    <th>Current Leaf Category</th>
    <th>Action</th>
  </tr>
  <?php foreach($invalidProducts as $prod): ?>
  <tr>
    <td><?= $prod['id'] ?></td>
    <td><?= htmlspecialchars($prod['product_name']) ?></td>
    <td><?= $prod['main_cat'] ?></td>
    <td><?= $prod['sub_cat'] ?></td>
    <td><?= $prod['leaf_cat'] ?></td>
    <td><a href="edit_product_cats.php?id=<?= $prod['id'] ?>">Fix Categories</a></td>
  </tr>
  <?php endforeach; ?>
</table>

4. Optimize Indexes for Faster Audits #

Keep your indexes working efficiently for both product browsing and audit queries:

• Add a composite index to your categories_tbl for fast parent-child lookups:

  CREATE INDEX idx_cat_parent_level ON categories_tbl(parent_id, level);
  

• Add a composite index to products_tbl to speed up filtering unreviewed products by category:

  CREATE INDEX idx_reviewed_cats ON products_tbl(is_reviewed, main_category, sub_category, leaf_category);
  

5. Optional: Automated Category Suggestions #

Reduce review workload by flagging products where the user’s category selection doesn’t match keywords in the product title/description:

<?php
$productTitle = $_POST['product_name'];
$suggestedMainCat = null;

// Map keywords to category IDs
$keywordMappings = [
  1 => ['phone', 'smartphone', 'mobile'], // Main category: Electronics > Phones
  2 => ['laptop', 'notebook', 'computer'], // Main category: Electronics > Laptops
  // Add more mappings as needed
];

foreach($keywordMappings as $catId => $keywords) {
  foreach($keywords as $keyword) {
    if (stripos($productTitle, $keyword) !== false) {
      $suggestedMainCat = $catId;
      break 2;
    }
  }
}

// If suggested category doesn't match user's choice, flag for review
if ($suggestedMainCat && $suggestedMainCat != $mainCatId) {
  $reviewNotes = "Suggested main category ID: $suggestedMainCat (matches title keywords)";
  // Save product with is_reviewed=0 and these notes
}
?>

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