CODEWITHSUNDEEP
php
David
David

Reputation: 409

Option values hidden if present in database

I'm trying to write a script that will check the database and not display option values if they are already present in the database but im not sure how to go about it. Below is what I have so far. Any help would be apprcaited. I've looked though several similar topics on here but they did not really fit what I was looking to do. I also put an example table below to better explain what im looking for. So if a pseron have flag 2 and 3 assigned to them only option 1 will be visable.

<?php
                $flag["infractions"] = [];
                $sql = "SELECT *
FROM flag_info
INNER JOIN infraction_flags 
ON flag_info.flag_id = infraction_flags.flag_id
WHERE ban_id = ?;";
                $stmt = $conn->prepare($sql);
                $stmt->bind_param("s", $banId);
                $stmt->execute();
                $result = $stmt->get_result();
                if ($result->num_rows != 0)
                {
                    echo "<table width='85%'>";
                    while ($row = $result->fetch_assoc())
                    {
                        $id = $row['flag_id'];
                        $longName = $row['flag_long_name'];
                        echo "<tr>";
                        echo "<td>";
                        echo $longName . "&nbsp";
                        echo "</td>";
                        echo "<form action='includes/deleteFlags.inc.php' method='POST'>";
                        echo "<input type='hidden' name='ban_id' value='" . $banId . "'>";
                        echo "<input type='hidden' name='flag_id' value='" . $id . "'>";
                        echo "<td>";
                        echo "<input type='submit' name='delete' value='[X]'></td>";
                    }
                }
                echo "</tr>";
                echo "</table>";
                echo "</form>";
                echo "<hr>";
                $sql3 = "SELECT * FROM flag_info";
                $stmt = $conn->prepare($sql3);
                $stmt->execute();
                $result = $stmt->get_result();
                echo "<form action='includes/assignFlags.inc.php' method='POST'>";
                echo "<select name='flag_id'>";
                while ($flg = $result->fetch_assoc())
                {   
                    if ($flg['flag_id'] = $id) {   //This is the logic to display the option values                 
                     echo "<option value='" . $flg['flag_id'] . "'>" . $flg['flag_long_name'] . "</option>";   
                }
                }
                echo "</select>";
                echo "<input type='hidden' name='ban_id' value='" . $banId . "' size='1'>";
                echo "<input type='submit' name='submit'>";
                echo "</form>";

infraction_flags table

+--------+--------+---------+
| id     | ban_id | flag_id |
+========+========+=========+
|1       | 15     | 3       |
+--------+--------+---------+ 
|2       | 15     | 2       |
+--------+--------+---------+
|3       | 3      | 1       |
+--------+--------+---------+  
|4       | 1      | 1       |
+--------+--------+---------+

Upvotes: 0

Views: 55

Answers (2)

user8486381
user8486381

Reputation:

You can first get all the ID and put into an array. see $flag_ids I've put in your code. and make implode it to make it like 1, 2, 3, 4, etc... then now select your flag and filter using WHERE NOT IN

OPTION 1

<?php

$flag["infractions"] = [];
$sql = "SELECT *
FROM flag_info
INNER JOIN infraction_flags 
ON flag_info.flag_id = infraction_flags.flag_id
WHERE ban_id = ?;";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $banId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows != 0) {
    $flag_ids = array();
    echo "<table width='85%'>";
    while ($row = $result->fetch_assoc()) {
        $flag_ids[] = $row['flag_id']; //put all the flag id inside an array
        $longName = $row['flag_long_name'];
        echo "<tr>";
        echo "<td>";
        echo $longName . "&nbsp";
        echo "</td>";
        echo "<form action='includes/deleteFlags.inc.php' method='POST'>";
        echo "<input type='hidden' name='ban_id' value='" . $banId . "'>";
        echo "<input type='hidden' name='flag_id' value='" . $id . "'>";
        echo "<td>";
        echo "<input type='submit' name='delete' value='[X]'></td>";
    }
}
echo "</tr>";
echo "</table>";
echo "</form>";
echo "<hr>";
if(!empty($flag_ids)) {
    $where_in = implode(',', ); //set the array like 1, 2, 3, 4
    $sql3 = "SELECT * FROM flag_info WHERE flag_id NOT IN(" . $where_in . ")";
    $stmt = $conn->prepare($sql3);
    $stmt->execute();
    $result = $stmt->get_result();
    echo "<form action='includes/assignFlags.inc.php' method='POST'>";
    echo "<select name='flag_id'>";
    while ($flg = $result->fetch_assoc()) {
        echo "<option value='" . $flg['flag_id'] . "'>" . $flg['flag_long_name'] . "</option>";
    }
    echo "</select>";
    echo "<input type='hidden' name='ban_id' value='" . $banId . "' size='1'>";
    echo "<input type='submit' name='submit'>";
    echo "</form>";
}

OPTION 2: If you still want to show the select even option is empty

<?php

$flag["infractions"] = [];
$sql = "SELECT *
FROM flag_info
INNER JOIN infraction_flags 
ON flag_info.flag_id = infraction_flags.flag_id
WHERE ban_id = ?;";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $banId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows != 0) {
    $flag_ids = array();
    echo "<table width='85%'>";
    while ($row = $result->fetch_assoc()) {
        $flag_ids[] = $row['flag_id']; //put all the flag id inside an array
        $longName = $row['flag_long_name'];
        echo "<tr>";
        echo "<td>";
        echo $longName . "&nbsp";
        echo "</td>";
        echo "<form action='includes/deleteFlags.inc.php' method='POST'>";
        echo "<input type='hidden' name='ban_id' value='" . $banId . "'>";
        echo "<input type='hidden' name='flag_id' value='" . $id . "'>";
        echo "<td>";
        echo "<input type='submit' name='delete' value='[X]'></td>";
    }
}
echo "</tr>";
echo "</table>";
echo "</form>";
echo "<hr>";
$where_in = implode(',', ); //set the array like 1, 2, 3, 4
$where_in = empty($where_in) ? '0' : $where_in;
$sql3 = "SELECT * FROM flag_info WHERE flag_id NOT IN(" . $where_in . ")";
$stmt = $conn->prepare($sql3);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows != 0) {
  echo "<form action='includes/assignFlags.inc.php' method='POST'>";
  echo "<select name='flag_id'>";
  while ($flg = $result->fetch_assoc()) {
      echo "<option value='" . $flg['flag_id'] . "'>" . $flg['flag_long_name'] . "</option>";
  }
}
echo "</select>";
echo "<input type='hidden' name='ban_id' value='" . $banId . "' size='1'>";
echo "<input type='submit' name='submit'>";
echo "</form>";

Upvotes: 1

Mainul Hasan
Mainul Hasan

Reputation: 699

Use MySQL inner join. And set where condition flag_id = 2 or flag_id = 3

Upvotes: 0

Related Questions