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

3

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>";
}

answered Jul 1, 2015 at 15:06

Daan

12.3k6 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

oliverbj Over a year ago

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?

Daan Over a year ago

Add a HAVING clause

oliverbj Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related