Create two related drop down lists from mysql database

PHP
1

I need to create two related drop down lists using data from a mysql database using only php, no javascript allowed. All the posts I have seen involve using javascript which my spec does not allow me to do. I can generate one drop down list but do not know how to then generate a related drop down list which I can use to choose an item to edit.
This is what I mean - generate a drop down list of companies from a mysql table, then having selected a company generate a list of customers in that company (in same table) and then choose which customer’s details to edit.
thanks for any help

2

The basic logic is:

  1. Select companies list from the DB
  2. If $_GET[‘company’] is set then select list of products for the company
  3. Generate a drop down (name=“company”) with the list of companies (if $_GET[‘company’] is set then use it as currently selected company; selected=“selected”)
  4. If $_GET[‘company’] is set then generate a drop down with a list of products for that company.
3

It isn’t possible to do this with PHP alone if you want the customers to appear straight away.

However it’s certainly possible to do it if you are willing to have a form submission:


<?php
$CompanyOptions = array();
$CustomerOptions = array();
$CompanyQueryString = 'SELECT id, name FROM companies';
$CompanyQuery = MySQL_Query($CompanyQueryString) or exit('Error in MySQL Query.<br />Query: ' . $CompanyQueryString . '<br />Error: ' . MySQL_Error());
while($Company = MySQL_Fetch_Assoc($CompanyQuery)){
    $CompanyOptions[$Company['id']] = $Company['name'];
}
if(array_key_exists('Company', $_POST)){
    $CustomerQueryString = 'SELECT id, name FROM customers WHERE company = ' . (int)$_POST['Company'];
    $CustomerQuery = MySQL_Query($CustomerQueryString) or exit('Error in MySQL Query.<br />Query: ' . $CompanyQueryString . '<br />Error: ' . MySQL_Error());
    while($Customer = MySQL_Fetch_Assoc($CustomerQuery)){
        $CustomerOptions[$Customer['id']] = $Customer['name'];
    }    
}
?>
<form method="post">
    <select name="Company">
        <?php
        foreach($CompanyOptions as $ID => $Name){
            PrintF('<option value="&#37;s">%s</option>', $ID, $Name);
        }
        ?>
    </select>
    <input type="Submit" value="Update" />
    <select name="Customer">
        <?php
        foreach($CustomerOptions as $ID => $Name){
            PrintF('<option value="%s">%s</option>', $ID, $Name);
        }
        ?>
    </select>
</form>

Just a quick example, but you should get the picture :stuck_out_tongue:

4

Thanks for the example! However, the related drop down list doesn’t work. I’ve substituted my column names for those in the example and using the code below i get a drop down list for companies with an update button alongside and then a blank field where the related drop down list should go, pressing the update list only results in the company list going back to the first one in the list. Am I doing something stupid? By the way both company and contact name are in the same table and customer_id is the primary key.

$CompanyOptions = array();
$CustomerOptions = array();
$CompanyQueryString = 'SELECT customer_id, company FROM tbl_customer';
$CompanyQuery = MySQL_Query($CompanyQueryString) or exit('Error in MySQL Query.<br />Query: ' . $CompanyQueryString . '<br />Error: ' . MySQL_Error());
while($Company = MySQL_Fetch_Assoc($CompanyQuery)){
    $CompanyOptions[$Company['customer_id']] = $Company['company'];
}
if(array_key_exists('company', $_POST)){
    $CustomerQueryString = 'SELECT customer_id, contact_name FROM tbl_customer WHERE company = ' . (int)$_POST['Company'];
    $CustomerQuery = MySQL_Query($CustomerQueryString) or exit('Error in MySQL Query.<br />Query: ' . $CompanyQueryString . '<br />Error: ' . MySQL_Error());
    while($Customer = MySQL_Fetch_Assoc($CustomerQuery)){
        $CustomerOptions[$Customer['id']] = $Customer['contact_name'];
    }    
}
?>
<form method="post">
    <select name="Company">
        <?php
        foreach($CompanyOptions as $customer_id => $company){
            PrintF('<option value="%s">%s</option>', $customer_id, $company);
        }
        ?>
    </select>
    <input type="Submit" value="Update" />
    <select name="Customer">
        <?php
        foreach($CustomerOptions as $customer_id => $contact_name){
            PrintF('<option value="%s">%s</option>', $contact_id, $contact_name);
        }
        ?>
    </select>
</form>
5

What I gave you was merely an example - it is completely untested.

However, to fix it from what you have so far, I’m assuming the problem is that you are checking if $_POST[‘company’] exists but should be checking if $_POST[‘Company’] exists, in the first ‘if’ statement.

As for maintaining the current value of the company in the first select box - well, I’m not going to do all of your work for you :stuck_out_tongue: Just add an if statement in the loop checking if the value you’re on is the value that was sent through $_POST (checking, beforehand, if anything was actually sent), and if so append the correct HTML in the option to make it the selected value.