SQL多表查询优化及按gouvernorat名称更新tarifs_zone价格问询

我来帮你解决这两个问题，先从优化查询逻辑、实现去重开始，再讲如何实现根据两个gouvernorat更新prix的功能：

注意：原代码直接拼接SQL存在严重的SQL注入风险，这是非常危险的，下面的优化方案会改用PDO预处理语句来解决这个问题。

原函数的子查询冗余，导致逻辑复杂，而且没有实现排除(B,A)重复组合的需求。我们可以通过多次JOIN关联表来简化SQL，同时添加去重条件，并且用预处理语句保证安全：

public function Get_zones($start, $length, $order, $dir, $search, $gouvernorat) {
    // 用表别名简化SQL，关联所有需要的表，避免子查询
    $sql = 'SELECT 
                g_a.nom AS gouvernoratA,
                d_a.nom AS delegationA,
                g_b.nom AS gouvernoratB,
                d_b.nom AS delegationB,
                tz.prix AS prix,
                tz.zone_a AS zoneA,
                tz.zone_b AS zoneB
            FROM tarifs_zones tz
            JOIN delegation d_a ON tz.zone_a = d_a.id
            JOIN gouvernorat g_a ON d_a.id_gouvernorat = g_a.id
            JOIN delegation d_b ON tz.zone_b = d_b.id
            JOIN gouvernorat g_b ON d_b.id_gouvernorat = g_b.id
            -- 去重核心条件：只保留zone_a < zone_b的组合，自动排除(B,A)的重复项
            WHERE tz.zone_a < tz.zone_b
            AND (
                -- 筛选指定gouvernorat的条件，支持"所有gouvernorat"选项
                g_a.nom = :gouvernorat 
                OR g_b.nom = :gouvernorat 
                OR :gouvernorat = \'Tous les gouvernorats\'
            )
            AND (
                -- 多字段搜索条件
                d_a.nom LIKE :search 
                OR g_a.nom LIKE :search 
                OR d_b.nom LIKE :search 
                OR g_b.nom LIKE :search 
                OR CAST(tz.prix AS CHAR) LIKE :search
            )
            ORDER BY :order :dir 
            LIMIT :start, :length';

    // 初始化预处理语句
    $stmt = $this->db->prepare($sql);
    
    // 绑定参数，搜索参数需要添加%通配符
    $searchParam = "%{$search}%";
    $stmt->bindParam(':gouvernorat', $gouvernorat, PDO::PARAM_STR);
    $stmt->bindParam(':search', $searchParam, PDO::PARAM_STR);
    $stmt->bindParam(':order', $order, PDO::PARAM_STR);
    $stmt->bindParam(':dir', $dir, PDO::PARAM_STR);
    $stmt->bindParam(':start', $start, PDO::PARAM_INT);
    $stmt->bindParam(':length', $length, PDO::PARAM_INT);
    
    $stmt->execute();
    // 返回关联数组格式的结果
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

关键优化点： #

• 用表别名让SQL结构更清晰，避免嵌套子查询
• WHERE tz.zone_a < tz.zone_b 确保每个无序区域组合只出现一次，完美解决重复问题
• 改用PDO预处理语句绑定参数，彻底杜绝SQL注入风险
• 搜索条件中把prix转为字符串，支持对价格的模糊搜索

这里分两种常见场景，你可以根据实际需求选择：

场景1：精确更新有序组合（仅更新GouvernoratA → GouvernoratB的记录） #

只更新zone_a属于第一个gouvernorat、zone_b属于第二个gouvernorat的所有tarifs记录：

public function UpdateTarifsByOrderedGouvernorats($gouvernoratA, $gouvernoratB, $newPrix) {
    $sql = 'UPDATE tarifs_zones tz
            JOIN delegation d_a ON tz.zone_a = d_a.id
            JOIN gouvernorat g_a ON d_a.id_gouvernorat = g_a.id
            JOIN delegation d_b ON tz.zone_b = d_b.id
            JOIN gouvernorat g_b ON d_b.id_gouvernorat = g_b.id
            SET tz.prix = :newPrix
            WHERE g_a.nom = :gouvernoratA 
            AND g_b.nom = :gouvernoratB';

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':gouvernoratA', $gouvernoratA, PDO::PARAM_STR);
    $stmt->bindParam(':gouvernoratB', $gouvernoratB, PDO::PARAM_STR);
    // 如果prix是浮点数，可改用PDO::PARAM_FLOAT或PDO::PARAM_STR
    $stmt->bindParam(':newPrix', $newPrix, PDO::PARAM_INT);
    
    // 返回执行结果（成功返回true，失败返回false）
    return $stmt->execute();
}

场景2：更新无序组合（同时更新(A,B)和(B,A)的记录） #

如果需要不管区域顺序，只要两个区域分别属于指定的两个gouvernorat就更新，用这个函数：

public function UpdateTarifsByUnorderedGouvernorats($gouvernoratX, $gouvernoratY, $newPrix) {
    $sql = 'UPDATE tarifs_zones tz
            JOIN delegation d_a ON tz.zone_a = d_a.id
            JOIN gouvernorat g_a ON d_a.id_gouvernorat = g_a.id
            JOIN delegation d_b ON tz.zone_b = d_b.id
            JOIN gouvernorat g_b ON d_b.id_gouvernorat = g_b.id
            SET tz.prix = :newPrix
            WHERE (
                (g_a.nom = :gouvernoratX AND g_b.nom = :gouvernoratY)
                OR (g_a.nom = :gouvernoratY AND g_b.nom = :gouvernoratX)
            )';

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':gouvernoratX', $gouvernoratX, PDO::PARAM_STR);
    $stmt->bindParam(':gouvernoratY', $gouvernoratY, PDO::PARAM_STR);
    $stmt->bindParam(':newPrix', $newPrix, PDO::PARAM_INT);
    
    return $stmt->execute();
}

使用说明： #

• 两个函数都通过JOIN关联到gouvernorat表，根据名称精准筛选对应的区域组合
• 同样用预处理语句绑定参数，保证操作安全
• 执行后返回布尔值，可用来判断更新是否成功

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