PHP array into individual php string, from SQL data

Here is a PHP code, that can be used to count data from SQL. This code displays the data in a table, I want to convert these table values (or arrays) into individual php strings. How can I do that ? I tried many methods, main issue is my lack of proper knowledge in PHP. I hope someone will help !

<?php
    $hostname="your_hostname";
    $username="your_username";
    $password="your_password";
    $db = "your_dbname";
    $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
    foreach($dbh->query('SELECT country,COUNT(*)
    FROM author
    GROUP BY country') as $row) {
    echo "<tr>";
    echo "<td>" . $row['country'] . "</td>";
    echo "<td>" . $row['COUNT(*)'] . "</td>";
    echo "</tr>"; 
    }
    ?>

and the result is

+-----------+----------+
| country   | COUNT(*) |
+-----------+----------+
| Australia |        2 | 
| Brazil    |        1 | 
| Canada    |        2 | 
| Germany   |        1 | 
| India     |        1 | 
| UK        |        4 | 
| USA       |        4 | 
+-----------+----------+

I want to use php string to use it anywhere else in the script/page. For example if I use $Australia or $value[0] , the string value should be “2“, how can I achieve that ?

Answer

With your current approach you can create a new array in your foreach, then use that array elsewhere.

foreach($dbh->query('SELECT country,COUNT(*) FROM author GROUP BY country') as $row) {
    $countries[$row['country']] = $row['COUNT(*)'];

elsewhere you’d use $countries['Australia']. You could use strtolower as well so keys are easier to match up.

Another approach though may be using fetchall.

$sth = $dbh->query('SELECT country,COUNT(*) FROM author GROUP BY country');
$result = $sth->fetchAll();
foreach($result as $row){
    //same iteration as before
}

with this approach you won’t necessarily know the keys. If you want the country with most/least views though this could be easier.