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

Seems ok? Check you have the values you expect by doing echo "<pre>";print_r($dept_positions);echo"</pre>"; — Antony Thompson
– Antony Thompson, Commented Nov 23, 2016 at 11:02
you are inside of a while the foreach is useless in this case. — Rafael Shkembi
– Rafael Shkembi, Commented Nov 23, 2016 at 11:03
@RafaelShkembi That's true, should be echo "<option value=\"$dept_positions\">$dept_positions</option>"; — Antony Thompson
– Antony Thompson, Commented Nov 23, 2016 at 11:04

Rafael Shkembi · Accepted Answer · 2016-11-23 11:11:31Z

2

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

answered Nov 23, 2016 at 11:11

Rafael Shkembi

7666 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Armitage2k Over a year ago

thank you, but neither solution works for me. I have a select field with the multiple element, if I follow your 2nd option, the select field remains empty. If I use option 1, it populates only the first position of the concerned department, but misses the others. I updated my code to reflect the changes. Thank you!

Rafael Shkembi Over a year ago

ok in that case you can use the first one but you will need to modify the query. Let me update my code

Rafael Shkembi Over a year ago

change the query in first solution to this-> $position_query = ("SELECT distinct Positions FROM qci_departments where Department = '".$row['Department']."'");

Armitage2k Over a year ago

Just came across this: select2.github.io/examples.html#data it seems like Select2 only allows array population via JS or AJAX...

Armitage2k Over a year ago

Working now, I moved the entire PHP sql interaction to another file and sent the json request via AJAX. Works like a charm now, thanks a lot!

|

Collectives™ on Stack Overflow

PHP/MySQLi - display array data from table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related