Getting data from array to reference other table

  mysql, php

Im trying to get data from one table based upon the date in a column from another table. I’ve tried using an inner join but I can’t quite figure out how to make it do what I want. Below is an example. Basicly the numbers in the rules column will referance the rule_id in the infraction rules table. Then output the rule long name in readable text.

user_infractions table

+--------+-----------------+------------------+----------------+
|ban_id  | name            | username         | rules          |
+--------+-----------------+------------------+----------------+
|   2    | bob             | Bob              | 2,7,           |
|   7    | dave            | dave1            | 3,5,           |
+--------+-----------------+------------------+----------------+

infraction_rules table

+--------+-----------------+------------------+----------------+
|rule_id | rule number     | short_name       | long_name      |
+--------+-----------------+------------------+----------------+
|   2    | 1.2.2           | Rule 2           | Rule 2 Long Na |
|   7    | 1.2.7           | Rule 7           | Rule 7 Long Na |
+--------+-----------------+------------------+----------------+

Below is the code im using to try to achieve this

$string = $row['rules']; //data from table as a string
                $strtrim = rtrim($string,","); //removed trailing comma from string
                $violations = explode(",", $strtrim); // string to array conversion
                foreach ($violations as $violation) { //looping though array and outputting rule ids
                   $sql3 = "SELECT 
        ban_id, rules FROM user_infractions
        INNER JOIN infraction_rules 
        ON user_infractions.rules = infraction_rules.rule_id
        WHERE user_infractions.ban_id = ? AND user_infractions.rules = ?;";
        $stmt3 = $conn->prepare($sql3);
                $stmt3->bind_param("ss", $banId, $violation);
                $stmt3->execute();
                $result3 = $stmt3->get_result();
                print_r($result3);
                while ($row3 = $result3->fetch_assoc()) {
                    echo $row3['rule_long_desc'];//output to readable text
                }
                }

Source: Ask PHP

LEAVE A COMMENT