Sorting Into A Pulldown Menu Box Multiple Variables In Tiers

[JustinK101]

Hello,

I want to do a form selection pulldown menu box which sorts by multiple input variables. For example, the following is an example of the mysql datatable which holds all the data I want to put into the pulldown.

[code]
id            state                  city           company_name
0             California           San Diego       SD Supply
1             California           San Diego       SD Tech
2             California           Los Angles       LA Suppy
3             Texas                Austin              Austin Supply
[/code]

An example of the selection pulldown from this data, formatted and sorted how I want is:

[code]
-California
   -Los Angles
         LA Supply  
   -San Diego
         SD Supply
         SD Tech
-Texas
   -Austin
      Austin Supply
[/code]

See the states are sorted firstly, then under each state the citys are sorted and then each company name is sorted under each city. What is the block of php code to do this? I assume we first need to pull state and sort those results, then pull city and sort, and then pull company_name and sort.

Additional difficulties, I want to do error checking making sure they only select company_names and not cities or states. So selecting a state or city upon being processed would be an invalid choice.

Thanks for any help.

[JustinK101]

Here is what I have so far, its doesnt work of course.

[code]
<p>Company: <select name="company" id="company">
<?
    //HOW DO I ONLY PULL ONE OF EACH STATE, THIS PULLS DUPLICATE STATES, I ONLY WANT EACH UNIQUE STATE TO BE RETURNED.
    $sql_get_states = "SELECT state FROM pulldown_menu ORDER BY state";
    $result_states = mysql_query($sql_get_states);

    //HOW DO I ONLY PULL ONE OF EACH CITY, THIS PULLS DUPLICATE CITYS, I ONLY WANT EACH UNIQUE CITY TO BE RETURNED.
    $sql_get_citys = "SELECT city FROM pulldown_menu ORDER BY city";
    $result_citys = mysql_query($sql_get_citys);

    $sql_get_company_name = "SELECT company_name FROM pulldown_menu ORDER BY company_name";
    $result_company_names = mysql_query($sql_get_company_name);

    echo '<option value="">--- </option>';
                
    while ($row_states = mysql_fetch_assoc($result_states))
    {
        while($row_citys = mysql_fetch_assoc($result_citys))
        {
            while($row_company_names = mysql_fetch_assoc($result_company_names))
            {
                echo '<option value="' . $row_states[state] . '">' . $row_states[state] . '</option>';
                echo '<option value="' . $row_citys[city] . '">   ' . $row_citys[city] . '</option>';
                echo '<option value="' . $row_company_names[company_name] . '">      ' . $row_company_names[company_name] . '</option>';
            }
        }
    }
?>
</select>
[/code]

[ypirc]

Okay, I have created a solution for you, however, I HIGHLY advise you restructure your database. It's rather inefficient at the moment, and can be created in a much better fashion that will help you in the end. I suggest you break it up into 3 tables... example below...

table_1_states
[id][state name]
table_2_cities
[id][state_id][city_name]
table_3_companies
[id][city_id][company_name]

The above structure would help you a lot. Anyway, if you absolutely _refuse_ to change to a better database format, here is the solution I have come up with for your problem...I believe this is what you are looking for:

[code]
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
        $states[$row['state']][$row['city']][] = $row['company'];
}
foreach($states as $state => $value) {
        printf("<option value=\"%s\">%s</option>\n", $state, $state);
        foreach($states[$state] as $city => $value2) {
                printf("<option value=\"%s\">-%s</option>\n", $city, $city);
                foreach($states[$state][$city] as $company) {
                        printf("<option value=\"%s\">--%s</option>\n", $company, $company);
                }
        }
}
[/code]
output:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
%php -f states.php
<option value="California">California</option>
<option value="LA">-LA</option>
<option value="LA Supply">--LA Supply</option>
<option value="San Diego">-San Diego</option>
<option value="SD Supply">--SD Supply</option>
<option value="SD Tech">--SD Tech</option>
<option value="Texas">Texas</option>
<option value="Austin">-Austin</option>
<option value="Austin Supply">--Austin Supply</option>
[/quote]

[JustinK101]

ypirc,

Thanks for your help. Yeah I have no problems restructing my database. I have implemented the structure you recommended.

table_1_states
[id][state name]
table_2_cities
[id][state_id][city_name]
table_3_companies
[id][city_id][company_name]

So can you give me the code for this updated structure? Also any ideas how to do the error checking? Should I do it in javascript?

[ypirc]

[code]
$query = "SELECT states.state_name, GROUP_CONCAT(companies.company) AS company, cities.city_name FROM companies JOIN (cities, states) ON (companies.city_id=cities.id AND cities.state_id=states.id) GROUP BY city_name ORDER BY state_name,city_name";

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {
        $STATES[$row['state_name']][] = array('city'=>$row['city_name'], 'companies'=>$row['company']);
}
foreach($STATES as $state => $value) {
        printf("<option value=\"%s\">%s/option>\n", $state, $state);
        foreach($STATES[$state] as $value) {
                printf("<option value=\"%s\">-%s</option>\n", $value['city'], $value['city']);
                $companies = explode(',', $value['companies']);
                foreach($companies as $company) printf("<option value=\"%s\">--%s</option>\n", $company, $company);
        }
}
[/code]

[JustinK101]

When I add a company do I check if the city and state already in the state and city table? Or do I just keep adding and not worry about duplicates and the code you wrote takes care of it?

For example lets say I have a new company:

name = test company
state = California
City = San Diego

Lets say California is already listed in the states table but San Diego inst, how would I go about inserting these values into the db? Do I only insert the company and city, or do I insert all three?

Another case. Lets say I am inserting a company where the city and state already exists in the city and state tables, do I only insert the company name and point the city and state id's to the existing values?

BASICALLY DO I MAKE city, state, and company UNIQUE KEYS?

[ypirc]

Yes you should check if the state or city already exist. After checking if they exist, if they do not, insert the new state/city into the database. If they do exist, use the id number of the city within the database and correspond it to the company.

[JustinK101]

ypirc:

One problem, in my cities table I have each city being unique. This causes a problem because what happens if a city name is the same, but in two different states? It won't add the city at all, instead it references from the other WRONG state. How do I get around this? I can't think of a solution, but city needs to NOT be unique.

Thanks.

ypirc:

Acutally I am starting to think you idea of using three different databases, each relational is acutally a bad idea.

Because: Deletes are going to be difficult if I want to keep everything (states, cities) clean. If I need to delete a company I can delete the company, but then I need to somehow check if the nothing points to that city and if so then delete that city and then check if nothing points to that state. If so, then delete that state. You see what I mean?

Does it make sense to do a single table as I suggester eariler?

[code]
  id         state            city           company
[/code]

THen generate my insert, select, delete around this structure?

[ypirc]

No, why delete the city and state if you delete a company? It's better just to maintain the data for future use. Also, you don't _need_ the cities to be unique if they are from different states. You can add duplicate city names for different states and it will still function correctly.

[JustinK101]

[b]ypirc:[/b]

I decided to go with the easier database structure of:

id state city installer

Using your code:

[code]
$sql = "SELECT state, city, installer FROM installers";
    $result = mysql_query($sql);
    
    while($row = mysql_fetch_array($result))
    {
        $states[$row['state']][$row['city']][] = $row['installer'];
    }
    
    foreach($states as $state => $value)
    {
            printf("<option value=\"%s\">%s</option>\n", $state, $state);
            foreach($states[$state] as $city => $value2)
            {
                    printf("<option value=\"%s\">&nbsp;&nbsp;&nbsp;%s</option>\n", $city, $city);
                    foreach($states[$state][$city] as $installer)
                    {
                            printf("<option value=\"%s\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;%s</option>\n", $installer, $installer);
                    }
            }
    }
[/code]

Everything works right, but I would like the states to be sorted alphabetically, then under each state the cities are alphabetical and then under each city the list of installers are alphabetical. How can I acheive this? Here is a screen shot of what the code currrently does. Note, I think the states are alphabetical just by luck of how I entered them, but the cities and installers are not. Thank you very very much.

[img src=\"http://www.ftoperations.net/ss.jpg\" border=\"0\" alt=\"IPB Image\" /]

[JustinK101]

Ok I figured out the solution. I didnt know you can specify multiple sorts in mysql query itself.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$sql [color=orange]=[/color] [color=red]"[span style=\'color:blue;font-weight:bold\']SELECT[/color] state, city, installer [color=green]FROM[/color] [color=orange]installers[/color] [color=green]ORDER BY[/color] state, city, installer"[/span];
$result [color=orange]=[/color] mysql_query($sql);
[!--sql2--][/div][!--sql3--]