Solved

Populate dropdown menu with unique column entries

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
paypal ipn url 5 58
PHP Installer 5 24
how to use a switch statement with heredoc 11 23
Complex MySQL Query 2 18
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

778 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