Getting data from array to reference other table

Question

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) {
                   echo $violation;
               
                   $sql3 = "SELECT *
        FROM user_infractions
        INNER JOIN infraction_rules ON user_infractions.rules = infraction_rules.rule_id
        WHERE user_infractions.rules = ?;";
        $stmt3 = $conn->prepare($sql3);
                //$stmt -> mysqli_stmt_prepare($stmt, $sql);
                $stmt3->bind_param("s", $violation);
                $stmt3->execute();
                $result3 = $stmt3->get_result();
                if ($result3->num_rows > 0) {
                    while ($row3 = $result3->fetch_assoc()) {
                       echo $row3['rule_long_desc'];
                    }
                   }
               }

You need a bridge table between these two table because they both have many to many relationship this is the basic principle. — Noman Shaikh, Sep 25 '21 at 06:28
I was able to get the table relationship sorted out, Im having troble where more than one value is presnet in the rules column of user infractions. I think the query is looking at each commas sperated value as one big string, I need a way to makle the query look at one at a time and print the reuslts. I updated my code to relfect the changes. — David, Sep 25 '21 at 06:32
you sorted out in programming but in DB user_infractions table rules column still contains 2,7 and in infraction_rules table rules_id only contain 2 so in join condition never matched. Hope you get it — Noman Shaikh, Sep 25 '21 at 06:49
Is there a way to make the query read each comma seperated value one by one to produce the desiered result? — David, Sep 25 '21 at 06:56
No there is no native way in Mysql to get value. but this answer may help you https://stackoverflow.com/a/8585080/12226057 — Noman Shaikh, Sep 25 '21 at 07:01
But I strongly suggest you to create a bridge table that is the standard way to handle many to many relationships. — Noman Shaikh, Sep 25 '21 at 07:02

Getting data from array to reference other table

0 Answers0