PHP/MySQLi - display array data from table

Question

I have a form where the user can enter a departments name and then a multiple select box where they multiple employee positions / titles that are working in this department. The record is then saved to my SQL table via a foreach() loop, which is giving me this as a result:

I am now looking for a way to allow users to edit their submitted departments and positions, hence want to display the stored records again in a field, which leaves me with the problem of getting the array data back into the select field...

I have tried to use another foreach to populate the <select> field but no luck so far. I am not sure if the problem is just bad code or if the Select2 plugin is giving me problems here as well...

PHP (update2)

<?php
    // Start MySQLi connection
    $db = new mysqli($dbhost,$dbuser,$dbpass,$dbname);

    if($db->connect_errno > 0){
        die('Unable to connect to database [' . $db->connect_error . ']');
    }

    // Build basic query, Admins can see all records
    $sql = ("SELECT * FROM qci_departments GROUP BY Department ORDER BY Department");

    // run the query or show an error message
    if(!$result = $db->query($sql)){
        echo('There was an error selecting data from the table [' . $db->error . ']');
    }

    while($row = mysqli_fetch_array($result)){
        $dept_id = $row['ID'];
        $dept_name = $row['Department'];
        $dept_positions = $row['Positions'];
        echo "
            <tr>
                <td>
                    <input type=\"text\" class=\"form-control\" id=\"editDeptName\" name=\"editDeptName\" value=\"$dept_id\">
                </td>
                <td>$dept_name</td>
                <td>
                    <select id=\"editDeptPositions\" name=\"editDeptPositions\" class=\"form-control\" multiple>
                        <option value='".$dept_positions."'>".$dept_positions."</option>";
                        //$position_query = ("SELECT distinct Positions FROM qci_departments");
                        $position_query = ("SELECT distinct Positions FROM qci_departments where Department = '".$row['Department']."'");
                        if(!$result_positions = $db->query($position_query)){
                            echo('There was an error selecting data from the table [' . $db->error . ']');
                        } else {
                            while($row_positions = mysqli_fetch_assoc($result_positions)){
                                echo "<option value='".$row_positions['Positions']."'>".$row_positions['Positions']."</option>"; 
                            }
                        }
                    echo "</select>
                </td>
            </tr>";

Could anyone point me in the right direction please? Thank you, A2k

EDIT: updated the code to reflect changes suggested by the poster below

Answer 1

I will give you two solutions

First:

while($row = mysqli_fetch_array($result)){
    $dept_id = $row['ID'];
    $dept_name = $row['Department'];
    $dept_positions = $row['Positions'];
    echo "
        <tr>
            <td>
                <input type=\"text\" class=\"form-control\" id=\"editDeptName\" name=\"editDeptName\" value=\"$dept_id\">
            </td>
            <td>$dept_name</td>
            <td>
                <select id=\"editDeptPositions\" name=\"editDeptPositions\" class=\"form-control select2\">
                    <option value='".$dept_positions."'>".$dept_positions."</option>";
                    $position_query = ("SELECT distinct Positions FROM qci_departments");
                    if(!$result_positions = $db->query($position_query)){
                        echo('There was an error selecting data from the table [' . $db->error . ']');
                    } else {
                        while($row_positions = mysqli_fetch_assoc($result_positions)){
                            echo "<option value='".$row_positions['Positions']."'>".$row_positions['Positions']."</option>"; 
                        }
                    }
                echo "</select>
            </td>
        </tr>";
}

Make a second query inside the while to get the positions and populate the select options.

Second: //I prefer this one

$positions_arr = array();
$position_query = ("SELECT distinct Positions FROM qci_departments");
if(!$result_positions = $db->query($position_query)){
    echo('There was an error selecting data from the table [' . $db->error . ']');
} else {
    while($row_positions = mysqli_fetch_assoc($result_positions)){
        $positions_arr[] = $row_positions['Positions']; 
    }
}
while($row = mysqli_fetch_array($result)){
    $dept_id = $row['ID'];
    $dept_name = $row['Department'];
    $dept_positions = $row['Positions'];
    echo "
        <tr>
            <td>
                <input type=\"text\" class=\"form-control\" id=\"editDeptName\" name=\"editDeptName\" value=\"$dept_id\">
            </td>
            <td>$dept_name</td>
            <td>
                <select id=\"editDeptPositions\" name=\"editDeptPositions\" class=\"form-control select2\">
                    <option value='".$dept_positions."'>".$dept_positions."</option>";
                    foreach($positions_arr as $value){
                         echo "<option value='".$value."'>".$value."</option>";
                    }
                echo "</select>
            </td>
        </tr>";
}

Make a array of all positions and later make a foreach inside the while to populate the select options