PHP开发求助：如何为SQL查询添加Rank不等条件过滤

我是一名初级Web开发者，正在修改一段PHP代码，该代码从数据库获取数据并以动态表格形式展示。我对PHP和SQL较为陌生，现在需要指导如何为代码添加额外的过滤条件。

这段代码负责根据不同等级（Rank）获取并展示忠诚度规则、计划奖励（schedule rewards）、标签订阅者奖励（tag subscriber rewards）相关数据。我已实现部分过滤，但在添加基于特定等级条件的复杂过滤时遇到困难。

我尝试修改的相关代码片段：

// Fetch rule_title data from the joined dbc_loyalty_rules and rule_condition tables 
$ruleQuery = "SELECT dbc_loyalty_rules.rule_title
  FROM dbc_loyalty_rules
  INNER JOIN rule_condition ON dbc_loyalty_rules.id = rule_condition.rule_id
  WHERE dbc_loyalty_rules.status = 'active'
  AND (rule_condition.conditions LIKE '%\"rule_item\":\"Rank\",\"rule_operator\":\"=\",\"rule_value\":\"$rankName\"%'
      OR rule_condition.conditions NOT LIKE '%\"rule_item\":\"Rank\"%')";

需要实现以下两种场景：

1. 当条件为{"rule_type":"Member","rule_item":"Rank","rule_operator":"!=","rule_value":"Gold"}时，展示除Gold外的等级（Silver、Platinum、Tourist）对应数据；
2. 当条件为{"rule_type":"Member","rule_item":"Rank","rule_operator":"!=","rule_value":"Platinum"}时，展示除Platinum外的等级（Silver、Gold、Tourist）对应数据。

我不确定如何有效修改现有SQL查询以纳入这些条件，恳请指导如何将这些过滤逻辑整合到现有代码中，完整代码如下：

<?php
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$query = "SELECT * FROM dbc_rank";
$result = $conn->query($query);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $rankId = $row['id'];
        $rankName = $row['name'];

        echo '<table border="1" width="100%">';
        echo '<tr class="header-row">';
        echo '<th colspan="3" class="' . strtolower($rankName) . '-header">';
        echo $rankName;
        echo '<button class="collapsible-button" onclick="toggleContent(\'' . strtolower($rankName) . '-content\')"><i class="fa fa-chevron-up"></i></button>';
        echo '</th>';
        echo '</tr>';
        echo '<tr class="content-row ' . strtolower($rankName) . '-content">';
        echo '<th>Cron Campaign</th>';
        echo '<th>Schedule Campaign</th>';
        echo '<th>Rule Conditions</th>';
        echo '</tr>';

        // Fetch and display data from dbc_schedule_rewards with rank_id filter
        $scheduleQuery = "SELECT * FROM dbc_schedule_rewards WHERE status = 'Scheduled' AND filter_rank = $rankId";
        $scheduleResult = $conn->query($scheduleQuery);

        // Fetch and display data from dbc_tag_subscriber_rewards with rank_id filter
        $tagSubscriberQuery = "SELECT * FROM dbc_tag_subscriber_rewards WHERE status = 'Scheduled' AND rank_id = $rankId";
        $tagSubscriberResult = $conn->query($tagSubscriberQuery);

        // Fetch rule_title data from the joined dbc_loyalty_rules and rule_condition tables
        $ruleQuery = "SELECT dbc_loyalty_rules.rule_title 
        FROM dbc_loyalty_rules 
        INNER JOIN rule_condition ON dbc_loyalty_rules.id = rule_condition.rule_id 
        WHERE dbc_loyalty_rules.status = 'active' 
        AND (rule_condition.conditions LIKE '%\"rule_item\":\"Rank\",\"rule_operator\":\"=\",\"rule_value\":\"$rankName\"%'
             OR rule_condition.conditions NOT LIKE '%\"rule_item\":\"Rank\"%')";

        $ruleResult = $conn->query($ruleQuery);

        if ($scheduleResult->num_rows > 0 || $tagSubscriberResult->num_rows > 0 || $ruleResult->num_rows > 0) {
            $maxRows = max($scheduleResult->num_rows, $tagSubscriberResult->num_rows, $ruleResult->num_rows);

            for ($i = 0; $i < $maxRows; $i++) {
                echo '<tr class="content-row ' . strtolower($rankName) . '-content">';
                
                if ($i < $scheduleResult->num_rows) {
                    $scheduleRow = $scheduleResult->fetch_assoc();
                    echo '<td>' . $scheduleRow['title'] . '</td>';
                } else {
                    echo '<td></td>';
                }
                
                if ($i < $tagSubscriberResult->num_rows) {
                    $tagSubscriberRow = $tagSubscriberResult->fetch_assoc();
                    echo '<td>' . $tagSubscriberRow['title'] . '</td>';
                } else {
                    echo '<td></td>';
                }

                if ($i < $ruleResult->num_rows) {
                    $ruleRow = $ruleResult->fetch_assoc();
                    echo '<td>' . $ruleRow['rule_title'] . '</td>';
                } else {
                    echo '<td></td>';
                }
                
                echo '</tr>';
            }
        } else {
            echo '<tr class="content-row ' . strtolower($rankName) . '-content">';
            echo '<td colspan="3">No scheduled data found.</td>';
            echo '</tr>';
        }

        echo '</table>';
    }
} else {
    echo "No ranks found.";
}

$conn->close();
?>

1. 核心逻辑梳理 #

原查询已覆盖两种场景：规则明确匹配当前等级，或规则不包含Rank条件。现在需要新增处理!=操作符的情况——当规则设置为排除某等级时，当前等级不属于被排除范围的规则都要展示。

2. 修改SQL查询（LIKE匹配版） #

直接扩展原查询的条件分支，加入对!=操作符的判断：

$ruleQuery = "SELECT dbc_loyalty_rules.rule_title 
FROM dbc_loyalty_rules 
INNER JOIN rule_condition ON dbc_loyalty_rules.id = rule_condition.rule_id 
WHERE dbc_loyalty_rules.status = 'active' 
AND (
    -- 原逻辑：规则等于当前等级
    rule_condition.conditions LIKE '%\"rule_item\":\"Rank\",\"rule_operator\":\"=\",\"rule_value\":\"$rankName\"%'
    -- 新增：规则排除的不是当前等级
    OR (
        rule_condition.conditions LIKE '%\"rule_item\":\"Rank\",\"rule_operator\":\"!=\"%'
        AND rule_condition.conditions NOT LIKE '%\"rule_value\":\"$rankName\"%'
    )
    -- 原逻辑：规则无Rank条件
    OR rule_condition.conditions NOT LIKE '%\"rule_item\":\"Rank\"%'
)";

3. 更可靠的JSON函数写法（推荐） #

如果你的MySQL版本≥5.7，支持JSON操作函数，建议用JSON_EXTRACT解析条件，避免LIKE模糊匹配的误差：

$ruleQuery = "SELECT dbc_loyalty_rules.rule_title 
FROM dbc_loyalty_rules 
INNER JOIN rule_condition ON dbc_loyalty_rules.id = rule_condition.rule_id 
WHERE dbc_loyalty_rules.status = 'active' 
AND (
    -- 规则等于当前等级
    (
        JSON_EXTRACT(rule_condition.conditions, '$.rule_item') = '\"Rank\"'
        AND JSON_EXTRACT(rule_condition.conditions, '$.rule_operator') = '\"=\"'
        AND JSON_EXTRACT(rule_condition.conditions, '$.rule_value') = '$rankName'
    )
    -- 规则排除的不是当前等级
    OR (
        JSON_EXTRACT(rule_condition.conditions, '$.rule_item') = '\"Rank\"'
        AND JSON_EXTRACT(rule_condition.conditions, '$.rule_operator') = '\"!=\"'
        AND JSON_EXTRACT(rule_condition.conditions, '$.rule_value') != '$rankName'
    )
    -- 规则无Rank条件
    OR JSON_EXTRACT(rule_condition.conditions, '$.rule_item') IS NULL
)";

注：如果rule_condition.conditions是多条件JSON数组，需改用JSON_SEARCH查找是否包含Rank项，上述代码假设每个条件是单个JSON对象。

4. 安全修复：避免SQL注入 #

原代码直接拼接变量到SQL存在注入风险，必须改用预处理语句。以ruleQuery为例：

// 预处理查询语句
$ruleQuery = "SELECT dbc_loyalty_rules.rule_title 
FROM dbc_loyalty_rules 
INNER JOIN rule_condition ON dbc_loyalty_rules.id = rule_condition.rule_id 
WHERE dbc_loyalty_rules.status = 'active' 
AND (
    rule_condition.conditions LIKE ?
    OR (
        rule_condition.conditions LIKE '%\"rule_item\":\"Rank\",\"rule_operator\":\"!=\"%'
        AND rule_condition.conditions NOT LIKE ?
    )
    OR rule_condition.conditions NOT LIKE '%\"rule_item\":\"Rank\"%'
)";

// 绑定参数并执行
$stmt = $conn->prepare($ruleQuery);
$likeEqual = "%\"rule_item\":\"Rank\",\"rule_operator\":\"=\",\"rule_value\":\"$rankName\"%";
$likeNotValue = "%\"rule_value\":\"$rankName\"%";
$stmt->bind_param("ss", $likeEqual, $likeNotValue);
$stmt->execute();
$ruleResult = $stmt->get_result();

同理，scheduleQuery和tagSubscriberQuery也需要改成预处理语句，彻底消除注入风险。

内容的提问来源于stack exchange，提问作者SUGENTHIRAAN A L SANTHIRAN