[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Populate dropdown menu with unique column entries

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
BrighteyesDesign
Asked:
BrighteyesDesign
  • 3
  • 2
1 Solution
 
DerokorianCommented:
SELECT DISTINCT country.name,country.code FROM properties LEFT JOIN country ON country.code = properties.country_code

Open in new window

HTH
0
 
BrighteyesDesignAuthor Commented:
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
 
BrighteyesDesignAuthor Commented:
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
 
DerokorianCommented:
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
 
BrighteyesDesignAuthor Commented:
Perfect, thanks for that
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now