PHP - Foreach loop get results

Question

I have this function below, that should list all the country codes from my database, that is present, and then count how many times each country code is present:

function getAvailableCountries(){ global $dbh; $stmt = $dbh->prepare("SELECT country_code,country_name FROM users WHERE country_code!='NA' AND country_name!='NA'"); $stmt->execute(); $rows = $stmt->fetchAll(); foreach ($rows as $row){ $countryCode = $row["country_code"]; $countryName = $row["country_name"]; } print "<option value='$countryCode'>$countryName</option>"; }

the $countryCode variable consists of: USUSUSUSCA (since US is present 4 times in my database and CA is present one time) (The $countryName variable consists of the name of the countries, same format as above.)

The function then returns this:

<option value="US">United States of America</option>

My question is though, how can I get the available countries from my database, and then print them out, so each country is only present once?

Either by DISTINCT or GROUP BY

Robert
– Robert

2015-07-01 15:07:22 +00:00
Commented Jul 1, 2015 at 15:07 — Robert
– Robert, Commented Jul 1, 2015 at 15:07

Daan · Accepted Answer · 2015-07-01 15:06:39Z

Add DISTINCT to your query:

$stmt = $dbh->prepare("SELECT DISTINCT country_code,country_name FROM users WHERE country_code!='NA' AND country_name!='NA'");

You should also add print in your loop:

foreach ($rows as $row){ $countryCode = $row["country_code"]; $countryName = $row["country_name"]; print "<option value='$countryCode'>$countryName</option>"; }

Thanks! That did the trick. Can you tell me how to only show the result, IF there is more than 5 results in the database?
How would the query then look like? .. AND country_name!='NA' AND HAVING .. ?

Collectives™ on Stack Overflow

PHP - Foreach loop get results

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related