How to build a dropdown list from a database field

[eluser]Unknown[/eluser]
Hi,

Being a self-confessed newbie I'm looking for some advice and guidance

I have a table in my database called Dams which is simply a list of dams throughout the world. One of the fields within that table is called Country; the country that the named dam is in. Simple enough right!

Now I want to put a search form on my site allowing me to search through the dams, and for the Country field I want to present it as a dropdown list, listing all the countries that occur in that field, in alphabetical order with a number after each country's name with the number of times that country occurs in the field. For example, there are 15 dams in Brazil so I want the dropdown entry for Brazil to read 'Brazil (15 dams)'. If there is only 1 occurrence of a country then no bracketed number will be listed against it ('Kazakhstan' for instance).

How do I go about doing this?

I'm not looking for someone to give me the code (although that would be nice!) I'm just looking for pointers on where to start really.

I'm also scratching my head a bit wondering where to put everything. I get that the dropdown list goes in the view file and the database interaction goes in the model, but how to pass the relevant data between them...?

All help and advice is gratefully received.

Tony.

[eluser]cbwd[/eluser]
First of all, lookup form_dropdown() in http://ellislab.com/codeigniter/user-gui...elper.html if you haven't done already.

To get the content of the drop-down, use an SQL query. I'm assuming you know how to do this, if not have a go, and let me know if you get stuck (remember to post the code you tried and what problems you encountered).

Finally get the data from your query into an array and use that with your form_dropdown() to provide the options for your dropdown list.

[eluser]Unknown[/eluser]
Thanks for your reply cbwd.

Things have become more complicated since my original post, insofar as I've discovered that the dam countries saved in the database are just the country codes (US, GB, etc.) not the actual country names (United States, United Kingdom etc.) which is what I want to display in my dropdown.

So now I am at a total loss to know how to retrieve the country code and convert it into the appropriate country name.

I've created an array of country codes/names like so:

Code:

$countries = array(
  "AF" => "Afghanistan",
  "AL" => "Albania",
  "DZ" => "Algeria",
  "AS" => "American Samoa",
  ...
  "ZW" => "Zimbabwe"
);

And I've created the query below to retrieve the country field contents from the table, count how many of each country occurs, and group the results:

Code:

$result = $this->db->query("select country,count(*) as count from default_dams group by country")->result_array();

Now I've got to the point where I don't know how to proceed.
I'm guessing I need a foreach loop to go through $result and form an array of results to pass from my model to the view, but how to build that array?

By the way, the code snippet from my view file that displays the dropdown is:

Code:

$first_field = '><option value="all">Any Country</option';
echo form_dropdown('dam-country', $dropdown, 'all', $first_field);

As a reminder, what I'm looking to build in the dropdown is a series of options like:

Code:

<option value="AL">Albania (3 dams)</option>
<option value="CN">China (94 dams)</option>
<option value="ZW">Zimbabwe (1 dam)</option>

I know originally I said that if there was only 1 dam for a country then don't display any number in brackets, but after some thought I considered it may be confusing, so adding '(1 dam') is best.

I feel I've got the groundwork kind of there, but it's how to build off it that's stumping me!

Thanks again for your help,
Tony.

[eluser]cbwd[/eluser]
[quote author="Teecee" date="1332680664"]Thanks for your reply cbwd.

Things have become more complicated since my original post, insofar as I've discovered that the dam countries saved in the database are just the country codes (US, GB, etc.) not the actual country names (United States, United Kingdom etc.) which is what I want to display in my dropdown.

So now I am at a total loss to know how to retrieve the country code and convert it into the appropriate country name.

I've created an array of country codes/names like so:

Code:

$countries = array(
  "AF" => "Afghanistan",
  "AL" => "Albania",
  "DZ" => "Algeria",
  "AS" => "American Samoa",
  ...
  "ZW" => "Zimbabwe"
);

[/quote]

Personally I'd store the code/country conversion as a separate table then just use a JOIN in your SQL query to do the translation. More than one way to skin a cat though, it would be easy to foreach through your list of countries and do something like:

Code:

$country = $countries[$code];

[quote author="Teecee" date="1332680664"]
And I've created the query below to retrieve the country field contents from the table, count how many of each country occurs, and group the results:

Code:

$result = $this->db->query("select country,count(*) as count from default_dams group by country")->result_array();

[/quote]

Looks good

[quote author="Teecee" date="1332680664"]Thanks for your reply cbwd.
Now I've got to the point where I don't know how to proceed.
I'm guessing I need a foreach loop to go through $result and form an array of results to pass from my model to the view, but how to build that array?[/quote]

Have a look in http://ellislab.com/codeigniter/user-gui...sults.html under results_array(), it explains how to do it.

From this you'll want to build another array containing something like:

Code:

$countries = array(
  "afghanistan" => "Afghanistan (1 dam)",
  "albania" => "Albania (4 dams)",
  "algeria" => "Algeria (5 dams)",
  "american_samoa" => "American Samoa (4 dams)",
  ...
  "zimbabwe" => "Zimbabwe (3 dams)"
);

You'll use this in your form_dropdown().