I want to fetch data from database but it has alot of conditions

Question

I'm working as a junior web developer, and I'm trying to modify a PHP code snippet that fetches data from a database and displays it in a dynamic table format. I'm relatively new to PHP and SQL, so I need some guidance on how to add additional filters to this code.

The code is responsible for fetching and displaying data related to loyalty rules, schedule rewards, and tag subscriber rewards based on different ranks. I've successfully implemented some filters, but I'm struggling to add a more complex filter based on specific rank conditions.

Here's the relevant section of the code that I'm trying to modify:

// 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\"%')";

If the condition is {"rule_type":"Member","rule_item":"Rank","rule_operator":"!=","rule_value":"Gold"}, the data should be displayed for ranks other than Gold, i.e., Silver, Platinum, and Tourist.
Similarly, if the condition is {"rule_type":"Member","rule_item":"Rank","rule_operator":"!=","rule_value":"Platinum"}, the data should be displayed for ranks other than Platinum, i.e., Silver, Gold, and Tourist.

I'm not sure how to modify the existing SQL query to include these conditions effectively.

Could someone please guide me on how to incorporate these filters into my existing code? Any assistance would be greatly appreciated!

Here is my full code just in case

 <?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();
?>

Which SQL are you using? MySQL? PostgreSQL? SQL-Server? ... and which version? — Nick, Aug 29 '23 at 05:55
phpMyAdmin is not a flavour of SQL, it is a web interface to a MySQL database. So you are using MySQL, but which version? It will be in the "Database Server" section of the phpMyAdmin home page — Nick, Aug 29 '23 at 05:59
You should use prepared queries to protect yourself from SQL injection. See for example https://stackoverflow.com/q/9629328/9473764 and https://stackoverflow.com/q/60174/9473764 — Nick, Aug 29 '23 at 06:50

score 3 · Answer 1 · answered Aug 29 '23 at 07:07

Since you're using MySQL 5.7, you have access to JSON functions, which you should use instead of trying to parse JSON as text. For your test code, you would use something like:

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->>'$.rule_item' = 'Rank'
  AND (rule_condition->>'$.rule_operator' = '!=' AND rule_condition->>'$.rule_value' != :rankName
    OR rule_condition->>'$.rule_operator' = '=' AND rule_condition->>'$.rule_value' = :rankName)

Note as I mentioned in the comments, you should be using prepared statements to avoid SQL injection. In the above query I've assumed you are using PDO; :rankName would be a placeholder in the prepared statement for $rankName.

I've made a demo of how the JSON part of the query works on dbfiddle.

I want to fetch data from database but it has alot of conditions

1 Answers1