?
Solved

Populate dropdown menu with unique column entries

Posted on 2012-03-13
5
Medium Priority
?
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

743 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