How do I show each item in a multi-select dropdown as already selected if it is in the database?

Question

So I have a form that edits a Units already in my database. There is a multi-select dropdown where I can choose Suppliers to the Unit. I need the suppliers that are already assigned to the unit to come up as selected(highlighted) in the dropdown, so the user only has to ctrl+click new Suppliers they want to add to the Unit.

units_suppliers is the junction table I have set up that contains paired Unit and Supplier IDs to determine which supplier is assigned to which unit, and vice-versa.

This is the code I currently have, but it's not working. I created two different arrays based on different SQL queries and tried to compare whether they have the same Supplier name, and if so the "selected" attribute was added to the tag. But it doesn't seem to be working. Can anyone help me out?

EDIT: The queries both seem to produce the right results, so I think the problem is in my nested loops, but I can't figure out what's wrong. Thanks for any advice.

<td><select size=7 multiple="multiple" name="unitsuppliers[]">
<?php
$sql = "SELECT suppliers_id, supplier_name FROM suppliers GROUP BY supplier_name ORDER BY supplier_name";
$result = mysql_query($sql,$connection);

/* THIS PART FINDS OUT IF THE SUPPLIER SHOULD ALREADY BE HIGHLIGHTED IN THE LIST */
$sql2 = "SELECT suppliers.supplier_name 
            FROM suppliers, units_suppliers 
            WHERE units_suppliers.unit_user_id = ";
if(isset($old_details['unit_user_id'])){
    $sql2 .= " {$old_details['unit_user_id']} ";
} elseif(isset($_POST['old_unit_id'])){
    $sql2 .= " {$_POST['old_unit_id']} ";
} else {
    $sql2 .= " 0 ";
}
$sql2 .=    " AND suppliers.suppliers_id = units_suppliers.suppliers_id";

$result2 = mysql_query($sql2,$connection);

while ($row = mysql_fetch_array($result)) {
            echo "<option value=\"{$row['suppliers_id']}\" ";

            while ($in_junc_table = mysql_fetch_array($result2)) {
                foreach ($in_junc_table as $array){
                    if($array['supplier_name'] == $row['supplier_name']){
                        echo " selected ";
                    }
                }
            }
            echo " >"; 
            echo "{$row['supplier_name']}";                         
        }   
?>

You need to sanitize your data, please: http://stackoverflow.com/questions/601300/what-is-sql-injection — Rob W, Jun 25 '13 at 18:16
You can't just specify `selected`. In most browsers, you still need to specify some truthy value: `selected="selected"`. — Kyle, Jun 25 '13 at 18:18
Can't you JOIN the two tables? This should leave you with only the suppliers that are in both tables for conditions you set with the WHERE clause. — Sumurai8, Jun 25 '13 at 18:18
@RobW, any enterable field has a mysql_prep() function that is applied to it to stop SQL injects. Thank you for the tip regardless. — Sw1sh, Jun 25 '13 at 18:26
@Kyle I tried selected='selected' and it's still not working. Any other thoughts? — Sw1sh, Jun 25 '13 at 18:27
As addition: In this case I think you can select all suppliers from the `unit_suppliers` table for this unit_id and LEFT or RIGHT JOIN (must look in the docs which one) the `suppliers` table. This give you rows with all suppliers. For all suppliers that match a unit_supplier for your current unit_id the value for `unit_suppliers.supplier_id` is not NULL. For other suppliers this value is NULL. — Sumurai8, Jun 25 '13 at 18:29
@Sumurai8 I haven't tried that, as I'm fairly new to MySQL and haven't used JOINs before. I'll look it up. — Sw1sh, Jun 25 '13 at 18:30
Doesn't matter if you do LEFT or RIGHT - but they'll both pretty much work the same. If you join, however, you should explicitly select your columns from each table.. such as `SELECT tbl1.colA AS something, tbl2.colA AS somethingElse` -- so then you can check `$row['somethingElse']`, if it's not null. Also, check this out: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx — Rob W, Jun 25 '13 at 18:34

Ast Derek · Accepted Answer · 2013-06-25T18:54:55.960

Remember that mysql_* familiy of functions is now deprecated, try using PDO.

Seems you can use a JOIN statement and run a single query, please try this code:

$db = new PDO('mysql:host=localhost;dbname=<SOMEDB>','<USERNAME>','PASSWORD');
$statement = $db->prepare("SELECT s.suppliers_id AS id, s.supplier_name AS name, u.unit_user_id AS user_id FROM suppliers s LEFT JOIN units_suppliers u USING suppliers_id WHERE (u.unit_user_id = :user_id OR u.unit_user_id IS NULL) GROUP BY s.supplier_name ORDER BY s.supplier_name");

if (isset($old_details['unit_user_id'])) {
    $user_id = $old_details['unit_user_id'];
}
else if (isset($_POST['old_unit_id'])) {
    $user_id = $_POST['unit_user_id'];
}
else {
    $user_id = 0;
}

$statement->execute(array(
    ':user_id' => $user_id
));

$supliers = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($suppliers as $supplier) { ?>
    <option value="<?php echo intval($supplier['id']) ?>" <?php  echo ($supplier['user_id'] == $user_id ? 'selected="selected"' : '') ?>><?php echo htmlspecialchars($supplier['name']) ?></option>
<?php }

Without PDO:

/**
 * Escape values that will be part of the query
 */
if (isset($old_details['unit_user_id'])) {
    $user_id = intval($old_details['unit_user_id']);
}
else if (isset($_POST['old_unit_id'])) {
    $user_id = intval($_POST['unit_user_id']);
}
else {
    $user_id = 0;
}

$suppliers = array();

$query = "SELECT s.suppliers_id AS id, s.supplier_name AS name, u.unit_user_id AS user_id FROM suppliers s LEFT JOIN units_suppliers u USING suppliers_id WHERE (u.unit_user_id = $user_id OR u.unit_user_id IS NULL) GROUP BY s.supplier_name ORDER BY s.supplier_name";
$result = mysql_query($query,$connection);

if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        $suppliers []= $row;
    }

    mysql_free_result($result);
}

foreach ($suppliers as $supplier) { ?>
    <option value="<?php echo intval($supplier['id']) ?>" <?php  echo ($supplier['user_id'] == $user_id ? 'selected="selected"' : '') ?>><?php echo htmlspecialchars($supplier['name']) ?></option>
<?php }

I understand that mysql_* functions are no longer really used, but I am pretty new to PHP and it was the way I learned. It is too late to re-do the whole site, but I plan on learning that way once this is finished. If you could let me know what the older version of `$statement->execute(array( ':user_id' => $user_id )); $supliers = $statement->fetchAll(PDO::FETCH_ASSOC);` is I would appreciate it a lot. — Sw1sh, Jun 25 '13 at 18:47
I got it working! There was an error in the MySQL query where "USING" should have been "ON". This was the query I used `SELECT s.supplier_name, s.suppliers_id, u.unit_user_id FROM suppliers s LEFT JOIN units_suppliers u ON s.suppliers_id = u.suppliers_id WHERE (u.unit_user_id = $unit_id OR u.unit_user_id IS NULL) GROUP BY s.supplier_name ORDER BY s.supplier_name`, but thank you so much! — Sw1sh, Jun 25 '13 at 20:30
Hmmm, I seem to be having an issue now though where if I select a different unit to choose it's suppliers, the ones chosen for the previous unit aren't visible. Any thoughts? — Sw1sh, Jun 25 '13 at 20:51

How do I show each item in a multi-select dropdown as already selected if it is in the database?

1 Answers1