Choose only required data from PHP – SQL Group by array function

April 28, 2022

The following SQL PHP code shows count of country from database and displays it in a table.
Here is the code

foreach($dbh->query('SELECT country,COUNT(*)
FROM data_able
GROUP BY country') as $row) {    $array = array();
echo "<tr>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "</tr>";
}
Here is the result

Here is the result

I would like to use the data as $country1 and $count1.
For example $country1 will output "Andorra" and $country5 will be "India".
and $count5 should give "25" as result. How can I do that ?

>Solution :

Here is an example how to declare variables dynamicly:

$array = ['1', '2', '3', '4', '5'];

foreach ($array as $i) {
    ${'country' . $i} = $i;
}

echo $country2;

You just need to iterate through your query results with keeping some index on variable suffix (in your case a number).

// create a dynamic variables
$result = $dbh->query('SELECT country,COUNT(*) FROM data_able GROUP BY country');
$i = 1;
foreach ($result as $row) {
    ${'country' . $i} = $row['country'];
    ${'count' . $i} = $row['COUNT(*)'];
    $i++;
}

// example how to use them
$i = 1;
foreach($result as $row) {
    $array = array();
    echo "<tr>";
    echo "<td>" . ${'country' . $i} . "</td>";
    echo "<td>" .${'count' . $i} . "</td>";
    echo "</tr>";
    $i++;
}