0

This is a continuation on a previous question that was resolved. I have the following query that extracts the country and the count for each country. 

$query = mysqli_query($con, "SELECT COUNT(id) AS countCnt, country FROM users GROUP BY country ORDER BY COUNT(id) DESC");

I'm trying to get these in an array as follows so I can assign them as variables:

$country_text = ['USA', 'Canada', 'England', 'Australia', 'New Zealand', etc...];
$country_count = [15, 10, 5, 3, 2, etc...];

Once these are assigned I want to display them as follows:

    for($i = 0; $i < 195; $i++) {

    echo "['{$country_text[$i]}'" . "," . "{$country_count[$i]}],";
}

These three code snips together are it's entirety. The challenge I'm having is in creating the arrays & assigning them as variables.

Improve this question

2 Answers 2

Reset to default
1

You can achieve this with:

//do a query and run
$query = mysqli_query($con, "SELECT COUNT(id) AS countCnt, country FROM users GROUP BY country ORDER BY COUNT(id) DESC");

$countries = [];

//iterate through values from query
while($row = mysqli_fetch_assoc($query)) {
    $country = $row['country'];
    $countryCount = $row['countCnt'];
    //create and array with data, formatted as ['USA' => '15', 'Canada' => '10' , ...]
    $countries[$country] = $countryCount;
}

//Create both arrays
$country_text = array_keys($countries);
$country_count = array_values($countries);

Or, instead of doing

for($i = 0; $i < 195; $i++) {
    echo "['{$country_text[$i]}'" . "," . "{$country_count[$i]}],";
}

You can do

foreach($countries as $country => $count) {
    echo "[".$country." ," .$count."],";
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

hmmm, I'm not getting an error, but the Geo Map is not displaying anything. Basically this in the for each echo "['{$country_text[$i]}'" . "," . "{$country_count[$i]}],"; replaces this default in the API ['South America', 900], ['Canada', 550], etc.
It works if I limit this for($i = 0; $i < 6; $i++) to <6. It doesn't like the $i<195... right now there are only 6 countries listed in the dB b/c it's test & I'm working locally. But this code won't account for additional countries being added. why is $i<195 rejected?
If you have 6 value in this countries array how will this for loop work for 195? This would be a good idea if you use another table for country where you will save all country then left join this with your user table. Hope you will get your derise output
Yes, thanks @Mithu CN, I'm coming to this realization now. This needs to be structured differently if my code is to account for the addition of other countries.
0

Format that you need is called json. Do not invent the wheel, everything is already invented:

$query = mysqli_query($con, "SELECT COUNT(id) AS countCnt, country FROM users GROUP BY country ORDER BY COUNT(id) DESC");

$countries = [];
while($row = mysqli_fetch_assoc($query)) {
    $countries[] = [$row['country'], $row['countCnt']];
}
echo json_encode($countries);
Improve this answer

2 Comments

I get the country listed, but the count comes up as null with this.
Because field name is countCnt and not countryCnt.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.