Displaying Query Result ids in Select box

[eluser]Fielder[/eluser]
Let me set this up..

- My database has 2 tables; business and states.
- The states table has id(pk), state, and state_abbrev fields (and various others of no importance right now).
- The business table has id(pk), state_abbrev(fk), and business number fields (and various others of no importance right now).

So when i query for a particular business number from the business table, obviously the state_abbrev field returns the abbreviation. But in my view at the <select> element i don't want to show the abbreviation, I want to show the entire state name.

Code:

<select name="bus_state" style="width:150px;" class="required">
                        &lt;?php
                        //need to add code here to show queried state as selected by default.
                        //display all states from getAllStates function
                        foreach($states as $row)
                        {
                            echo '<option value="'.$row['state_abbreviation'].'">'.$row['state_name'].'</option>';
                        }
                        ?&gt;
                    </select>

Do i run a secondary query through my method retrieving the state name associated with that abbreviation, before my final output $this->load->view(business_show.php, $data);?

**Keep in mind that I want the state from the query as the <selected> item in the drop down, while still populating the rest of the dropdown with my getAllStates function

Code:

$data['states'] = $this->States->getAllStates();

Code:

function getAllStates()
    {
        $this->db->select('state_abbreviation, state_name');
        $query = $this->db->get('state');
        if ($query->num_rows() > 0)
            {
                return $query->result_array();
            }
    }

I understand that this is a common database schema utilizing foreign-keys like this, so I imagine there's already a common practice for this.

[eluser]slowgary[/eluser]
I'm having a hard time understanding what the problem is. Your select DOES show the full state name.

If what you're saying is, after they select a state they get sent to the next page where you wish to display the full state name... the answer is yes, you will need to do an additional query to translate the abbreviation to the full name. There are a few ways around it though.

Why not just display the abbreviation? The user already knows which state they selected. You could, alternatively, create your option values as a combination of abbraviation/state name, like so:

Code:

<select>
   <option value='ny_new york'>New York</option>
</select>

Then just split it up in your receiving script. That's kind of a dirty way to do it though, I would just recommend showing the abbreviation on the next page, or for that matter, just use the full name as the value to begin with.

[eluser]Fielder[/eluser]
The sample I showed in the first post does, but I've commented in it for myself to try to figure out how to display the name and not abbrev when showing business query results.
From the very beginning, the user selects a link associated with a particular business they want to edit. That link fires a query to pull down all the fields associated with that business, then displays all the returned results in an editable form so the user may update any one of the fields. So in the case of the state, the value that is pulled down from the database (from the business table) is a foreign key called state_abbrev, which would actually be MA as variable $state_abbrev. So in my select statement I have <option value="&lt;?=$state_abbrev; ?&gt;" selected>&lt;?=$state_abbrev; ?&gt;</option>.

But I dont want to show $state_abbrev inbetween the <option></option> - i cant assume the user will know whether MA is Maine, Massachusetts, Montana, etc... I want to show the states full name.

You're right, it does make practical sense to just store the full name when talking about states, but I've actually got other database situations where i'm dealing with various foreign keys when pulling down records.

I think it may have to do with my queries being JOIN or UNIONS.

Hope this makes sense. Thanks for the reply.

[eluser]slowgary[/eluser]

But I dont want to show $state_abbrev inbetween the <option></option> - i cant assume the user will know whether MA is Maine, Massachusetts, Montana, etc… I want to show the states full name.

I disagree. You CAN assume the user should know what the abbreviation is. Especially in a business environment, this should be assumed knowledge. BUT, if you really want to make it fool proof I understand and I would probably do the same.

You will need to do a JOIN or something, yes.

[eluser]Nicholai[/eluser]
Since you're editing the business record, you will need a join to get to the State data. I recommend using a field like businesses.state_ID as a foreign key of states.ID instead of businesses.state_abbrev (joining on indexed number fields runs faster than indexed text fields), but that's a different discussion.

For your model that pulls the business data, add a simple join like this:

Code:

$this->db->join('States', 'States.state_abbreviation = Business.state_abbrev');

Now you can add the States.state_name to your SELECT list. If a business doesn't always have a state, be sure to use an outer join or you won't get any results.