从mySQLi Query创建数组以在php中显示

Question

I think it's better to ask these questions in parts rather than as one big question. 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. Any help would be greatly appreciated.

Answer 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."],";
}