数据库设计

首先，我们需要设计一个分类表，通常使用自关联的方式实现多级分类：

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `parent_id` int(11) DEFAULT 0 COMMENT '父级ID，0表示一级分类',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表';

PHP 实现代码

1. 数据库连接类 (Database.php)

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'your_database';
    private $username = 'your_username';
    private $password = 'your_password';
    private $conn;

    public function getConnection() {
        $this->conn = null;

        try {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->conn->exec("set names utf8mb4");
        } catch(PDOException $exception) {
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }
}
?>

2. 分类模型类 (Category.php)

<?php
class Category {
    private $conn;
    private $table_name = "categories";

    public $id;
    public $name;
    public $parent_id;
    public $sort_order;

    public function __construct($db) {
        $this->conn = $db;
    }

    // 获取所有一级分类
    public function getPrimaryCategories() {
        $query = "SELECT * FROM " . $this->table_name . " WHERE parent_id = 0 ORDER BY sort_order ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    // 获取指定父分类下的子分类
    public function getSubCategories($parent_id) {
        $query = "SELECT * FROM " . $this->table_name . " WHERE parent_id = ? ORDER BY sort_order ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $parent_id);
        $stmt->execute();
        return $stmt;
    }

    // 获取所有分类并按层级结构返回
    public function getAllCategoriesHierarchical() {
        // 获取所有分类
        $query = "SELECT * FROM " . $this->table_name . " ORDER BY parent_id ASC, sort_order ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        
        $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 构建层级结构
        $result = array();
        foreach ($categories as $category) {
            if ($category['parent_id'] == 0) {
                // 一级分类
                $result[$category['id']] = $category;
                $result[$category['id']]['children'] = array();
            } else {
                // 二级分类
                if (isset($result[$category['parent_id']])) {
                    $result[$category['parent_id']]['children'][] = $category;
                }
            }
        }
        
        return $result;
    }
    
    // 添加新分类
    public function create() {
        $query = "INSERT INTO " . $this->table_name . " SET name=:name, parent_id=:parent_id, sort_order=:sort_order";
        $stmt = $this->conn->prepare($query);
        
        // 清理数据
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->parent_id = htmlspecialchars(strip_tags($this->parent_id));
        $this->sort_order = htmlspecialchars(strip_tags($this->sort_order));
        
        // 绑定参数
        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":parent_id", $this->parent_id);
        $stmt->bindParam(":sort_order", $this->sort_order);
        
        if ($stmt->execute()) {
            return true;
        }
        return false;
    }
}
?>

3. 使用示例 (index.php)

<?php
header("Content-Type: text/html; charset=utf-8");

// 引入必要文件
require_once 'Database.php';
require_once 'Category.php';

// 初始化数据库连接
$database = new Database();
$db = $database->getConnection();

// 初始化分类对象
$category = new Category($db);

// 获取所有分类（层级结构）
$allCategories = $category->getAllCategoriesHierarchical();

// 显示分类
echo "<h2>分类列表</h2>";
echo "<ul>";
foreach ($allCategories as $primary) {
    echo "<li>" . $primary['name'];
    if (!empty($primary['children'])) {
        echo "<ul>";
        foreach ($primary['children'] as $secondary) {
            echo "<li>" . $secondary['name'] . "</li>";
        }
        echo "</ul>";
    }
    echo "</li>";
}
echo "</ul>";

// 添加新分类的表单
echo "<h2>添加新分类</h2>";
echo "<form method='post'>";
echo "分类名称: <input type='text' name='name' required><br>";
echo "父分类: <select name='parent_id'>";
echo "<option value='0'>一级分类</option>";

// 获取所有一级分类作为父分类选项
$stmt = $category->getPrimaryCategories();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>";
}

echo "</select><br>";
echo "排序: <input type='number' name='sort_order' value='0'><br>";
echo "<input type='submit' name='submit' value='添加分类'>";
echo "</form>";

// 处理表单提交
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['submit'])) {
    $category->name = $_POST['name'];
    $category->parent_id = $_POST['parent_id'];
    $category->sort_order = $_POST['sort_order'];
    
    if ($category->create()) {
        echo "<p>分类添加成功!</p>";
        // 刷新页面
        header("Refresh:0");
    } else {
        echo "<p>添加分类失败.</p>";
    }
}
?>

扩展功能

1. 分类编辑和删除：可以添加相应的方法来更新和删除分类

2. 无限级分类：修改算法以支持无限级分类

3. 分类路径：添加方法获取分类的完整路径

4. 缓存：对分类数据进行缓存以提高性能

安全注意事项

1. 始终对用户输入进行验证和过滤

2. 使用预处理语句防止SQL注入

3. 在生产环境中添加适当的错误处理

4. 考虑添加权限控制，限制谁可以管理分类

这个实现提供了一个基本的二级分类系统，你可以根据需要进行扩展和定制。