Solved

Populate dropdown menu with unique column entries

Posted on 2012-03-13
5
462 Views
Last Modified: 2012-03-14
For my real estate site I have two databases, one called 'Country' which lists all countries and their codes and another 'properties' which has all of the properties details. The properties table uses the country code from the 'Country' table (countrycode column) to state which country the property is from.

I need to create search function which just has a dropdown where you select a country. What query would I use to show a list of country names from the 'Country' table if the country code appears in the 'countrycode' column of the 'properties' table. Also each country should only display once. So the dropdown should show 2 options - United Kingdom and Turkey.
Screen-shot-2012-03-13-at-16.10..png
Screen-shot-2012-03-13-at-17.10..png
0
Comment
Question by:BrighteyesDesign
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Derokorian
ID: 37716007
SELECT DISTINCT country.name,country.code FROM properties LEFT JOIN country ON country.code = properties.country_code

Open in new window

HTH
0
 

Author Comment

by:BrighteyesDesign
ID: 37719149
Thanks for that,

$query_countries = "SELECT DISTINCT country.name,country.code FROM properties LEFT JOIN country ON country.code = properties.country_code"
?>

Open in new window


Would I then call the results using   <?php echo $query_countries['properties.name']; ?>
0
 

Author Comment

by:BrighteyesDesign
ID: 37719364
Also, using...

SELECT DISTINCT country.name,country.code FROM properties LEFT JOIN Country ON country.code = properties.country_code

Open in new window


...gives an error of "Unknown column 'country.name' in 'field list'"

Just to recap...

I have on table called 'Country' which has the column 'Code' and 'Name'

Then I have another table called 'properties' which has a column called 'country_code'

I need to display the name of the country (from Country table » Name column) if the country code is in the properties table 'country_code' column.

Then display all of these country names as a list (only once each).
0
 
LVL 10

Accepted Solution

by:
Derokorian earned 500 total points
ID: 37719688
I have on table called 'Country' which has the column 'Code' and 'Name'

Then I have another table called 'properties' which has a column called 'country_code'
Are your tables and columns really inconsistently capped? Are you on a *nix system? If these aren't both true, then I would ask you to do a SHOW CREATE TABLE `Country` and SHOW CREATE TABLE `properties` and paste it here so I can get the query working. If they are both true change the capitalization to match hopefully like so:
SELECT DISTINCT Country.Name,Country.Code FROM properties LEFT JOIN Country ON Country.Code = properties.country_code

Open in new window

Would I then call the results using   <?php echo $query_countries['properties.name']; ?>
You would access the results in a loop as with any MySQL result set like so:
$query = 'SELECT DISTINCT Country.Name,Country.Code FROM properties LEFT JOIN Country ON Country.Code = properties.country_code';
$result = mysqli_query($link,$query);
if( $result === FALSE ) {
   die('Query failed; MySQL said: '.mysqli_error($link));
}
if( mysqli_num_rows($result) > 0 ) {
   while( $row = mysqli_fetch_assoc($result) ) {
      echo $row['Name'];
      echo $row['Code'];
   }
} else {
   echo 'No results found';
}

Open in new window

HTH
0
 

Author Closing Comment

by:BrighteyesDesign
ID: 37719855
Perfect, thanks for that
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now