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
Solved

Populate dropdown menu with unique column entries

Posted on 2012-03-13
5
481 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

837 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