Populating Multiple Selection ListBox with data from 2 differnet tables

Question

So here is what i am trying to do. I have a table that contains a list of companies. I have another table that contains a list of companies for a specific employee. here are the tables

companies
company_id
name

employees_companies
empcomp_id
employee_id
company_id

Currently i have a list box that is populated with the list of all companies. When a user sets up a new employee they can choose one or more companies from the list box. When the form is submitted i create an array of all the selected companies, loop through them and add new records to the employees_companies table. This all works great. The problem i am having is on the form where a user can edit these companies. First i populate the list box with a list of all companies. Next i need to have the companies highlighted that are listed in the employees_companies table, for this user. Right now only one record is highlighted no matter how many were returned in the query.

Here is the code to get a list of all companies and the employees companies

// get list of all companies
mysql_select_db($database_freight, $freight);
$query_rsCompanyList = "SELECT * FROM companies ORDER BY scac ASC";
$rsCompanyList = mysql_query($query_rsCompanyList, $freight) or die(mysql_error());
$row_rsCompanyList = mysql_fetch_assoc($rsCompanyList);
$totalRows_rsCompanyList = mysql_num_rows($rsCompanyList);

// get employees companies
$colname_rsEmployeeCompanyList = "17";
if (isset($_GET['employee_id'])) {
  $colname_rsEmployeeCompanyList = $_GET['employee_id'];
}
mysql_select_db($database_freight, $freight);
$query_rsEmployeeCompanyList = sprintf("SELECT * FROM employees_companies WHERE employee_id = %s", GetSQLValueString($colname_rsEmployeeCompanyList, "int"));
$rsEmployeeCompanyList = mysql_query($query_rsEmployeeCompanyList, $freight) or die(mysql_error());
$row_rsEmployeeCompanyList = mysql_fetch_assoc($rsEmployeeCompanyList);
$totalRows_rsEmployeeCompanyList = mysql_num_rows($rsEmployeeCompanyList);

Here is the form

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>test</title>
</head>
<body>
<form id="frmAdd" name="frmAdd" method="post" action="test.php">
  <select name="company_id[]" size="4" multiple="multiple" id="company_id[]">
    <?php do { ?>
    <option value="<?php echo $row_rsCompanyList['company_id']?>"
    <?php
        do {
            if (!(strcmp($row_rsCompanyList['company_id'], $row_rsEmployeeCompanyList['company_id']))) {echo "selected=\"selected\"";}
        } while ($row_rsEmployeeCompanyList = mysql_fetch_assoc($rsEmployeeCompanyList)); ?>><?php echo $row_rsCompanyList['name']?></option>
    <?php
} while ($row_rsCompanyList = mysql_fetch_assoc($rsCompanyList));
  $rows = mysql_num_rows($rsCompanyList);
  if($rows > 0) {
      mysql_data_seek($rsCompanyList, 0);
      $row_rsCompanyList = mysql_fetch_assoc($rsCompanyList);
  }
?>
  </select>
  <input type="submit" name="button" id="button" value="Submit" />
</form>
</body>
</html>

=============================================================================== almost got it, but there is just one issue. The company name is not populated in the listbox. I noticed your code didn't have the closing option tag. i added it and entered $company_id for the value, so it is populating the listbox with the company_id, but how would i echo the company name instead.

Here is where i am creating the arrays

// get list of all companies
mysql_select_db($database_freight, $freight);
$query_rsCompanyList = "SELECT company_id, name FROM companies ORDER BY scac ASC";
$rsCompanyList = mysql_query($query_rsCompanyList, $freight) or die(mysql_error());

$all_company_ids = array();
while ($row_rsCompanyList = mysql_fetch_array($rsCompanyList)) {
  $all_company_ids[] = $row_rsCompanyList['compnay_id'];
}

// get list of all employees companies
$colname_rsEmployeeCompanyList = "17";
if (isset($_GET['employee_id'])) {
  $colname_rsEmployeeCompanyList = $_GET['employee_id'];
}
mysql_select_db($database_freight, $freight);
$query_rsEmployeeCompanyList = sprintf("SELECT company_id FROM employees_companies WHERE employee_id = %s", GetSQLValueString($colname_rsEmployeeCompanyList, "int"));
$rsEmployeeCompanyList = mysql_query($query_rsEmployeeCompanyList, $freight) or die(mysql_error());

$emp_company_ids = array();
while ($row_rsEmployeeCompanyList = mysql_fetch_array($rsEmployeeCompanyList)) {
  $emp_company_ids[] = $row_rsEmployeeCompanyList['compnay_id'];
}

Here is the form option

<?php foreach($all_company_ids as $company_id): ?>
  <option value="<?php echo $company_id; ?>" <?php if(in_array($company_id, $emp_company_ids)) echo "selected=\"selected\""; ?>><?php echo $company_id; ?></option>
<?php endforeach; ?>

Your code is so messed up. First, it would be nice, if you could use an ORM (unless you have a good reason not to!). Second, you are interacting with your DB in your form, which is not a good practice. — Omid Kamangar

Omid Kamangar Omid Kamangar · Accepted Answer · 2012-10-08T22:16:44

I think this is what you want: Display all the company ids and highlight only those which belong to an employee.
If I am right, here is a simpler way of doing it:

<?php
$all_company_ids = array(); // You should populate this as you wish, but it's better not to do it in your form!
$emp_company_ids = array(); // Again get this items from the database!
?>


<select name="company_id[]" size="4" multiple="multiple" id="company_id[]">
<?php foreach($all_company_ids as $company_id): ?>
  <option value="<?php echo $company_id; ?>" <?php if(in_array($company_id, $emp_company_ids)) echo "selected=\"selected\""; ?>></option>
<?php endforeach; ?>
</select>

Note that I didn't go into the details on how to get all the company ids or employee's company ids.

The idea is very simple:
You get all the company ids first. Then you loop through them and display each one as an option and if the item belongs to the employee, you select it!

I have not tried this code myself, but since the idea is simple, it should work fine.

EDIT For the company name, a simple way is to populate the $all_company_ids with the id and name of the company as key, value pairs like this:

$all_companies = array();
while ($row_rsCompanyList = mysql_fetch_array($rsCompanyList)) {
  $all_companies[$row_rsCompanyList['compnay_id']] = $row_rsCompanyList['name'];
}

And then you can display the name like this:

<select name="company_id[]" size="4" multiple="multiple" id="company_id[]">
<?php foreach($all_companies as $company_id => $company_name): ?>
    <option value="<?php echo $company_id; ?>" <?php if(in_array($company_id, $emp_company_ids)) echo "selected=\"selected\""; ?>><?php echo $company_name ?></option>
<?php endforeach; ?>
</select>

Note that I renamed the $all_company_ids variable to $all_companies. I fixed the option tag too.

Populating Multiple Selection ListBox with data from 2 differnet tables

1 Answers