Apologies for being really tardy with my example. As you’ll see, it’s a bit longer than @tlacaelelrl’s but I based it on his code. I’ll break it down to explain what I did as I go.
<?php
$dbUser = 'MySQL User';
$dbPass = 'MySQL User Password';
$dbHost = 'HostOrIp';
$dbName = 'DatabaseName';
$dbh = new PDO("mysql:dbname=$dbName;host=$dbHost", $dbUser, $dbPass);
$countries = array(
'United Kingdom',
'United States of America',
'France',
'Brazil'
);
$conditions = array();
$params = array();
for ($i=0; $i<count($countries); $i++){
$paramKey = ':country' . $i;
$conditions[] = $paramKey;
$params[$paramKey] = $countries[$i];
}
$condition = count($conditions)
? 'WHERE country IN(' . implode(',', $conditions) . ')'
: '';
$query = "
SELECT IFNULL(country, 'Total') AS country
, COUNT(*) AS count
FROM tbl_people
" . $condition . "
GROUP BY country
WITH ROLLUP";
$sth = $dbh->prepare($query);
$sth->execute($params);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
OK, so here’s what everything does:
$dbUser = 'MySQL User';
$dbPass = 'MySQL User Password';
$dbHost = 'HostOrIp';
$dbName = 'DatabaseName';
$dbh = new PDO("mysql:dbname=$dbName;host=$dbHost", $dbUser, $dbPass);
This simply creates a PHP Data Object that we’ll use to connect to the MySQL database. Change the variables to suit your needs
$countries = array(
'United Kingdom',
'United States of America',
'France',
'Brazil'
);
Simply a list of countries that we’re going to search on. Note that it’s bad practice to store countries like this. Really you should use either a lookup value and utilise foreign keys (ie UK = 0, USA = 1, France = 2) or you should use ISO country codes for consistency. But this will do for the example.
OK, so you can’t bind an array to a single parameter, so we can’t do this:
SELECT COUNT(*) FROM tbl_people WHERE country IN(:country) GROUP BY country
and then
$sth->execute(array('country' => $countries));
That will throw an error complaining about an array-to-string conversion. So we’ve worked around it with this:
$conditions = array();
$params = array();
for ($i=0; $i<count($countries); $i++){
$paramKey = ':country' . $i;
$conditions[] = $paramKey;
$params[$paramKey] = $countries[$i];
}
What this does is build an array of parameter names, and their associated values in another correctly-keyed array that can be passed straight to the PDO execute function. So your SQL condition will end up looking something like this:
WHERE country IN(:country0,:country1,:country2,:country3)
This is all done automagically. The $params array will then hold the values for each. You’ll see why I put them all in the $conditions array in the next piece of code.
$condition = count($conditions)
? 'WHERE country IN(' . implode(',', $conditions) . ')'
: '';
I put this in purely to support cases where no country is provided, in which case it will return all countries. Not necessary, but wanted to be a but more thorough. In here you can see that I used implode() on the $conditions array. I like to use this as it will correctly return each value with a separator, in this case a comma, which is the exact syntax that we need. There are other ways to do this, but I find this way the most robust, and it’s fast too.
$query = "
SELECT IFNULL(country, 'Total') AS country
, COUNT(*) AS count
FROM tbl_people
" . $condition . "
GROUP BY country
WITH ROLLUP";
$sth = $dbh->prepare($query);
So here’s your SQL, fully built. Note that $condition is included in here and will only have a value if you passed in any countries, otherwise it’ll be empty. Look at the last couple of lines as this is where the real differences are: Firstly, we “GROUP BY country”, which means that you’ll get a single row per country. Simple. Then the last line, where we apply the modifier “WITH ROLLUP”. What this does is to create a running total for each grouping level. Since we’ve only used one (country) you will get a single total at the end. The way that MySQL returns this row will be with a NULL value for the country name. You can intercept and manage with PHP if you like, but instead I’ve used the IFNULL() MySQL function on the first line, if you notice. Basically, to replace a NULL value with the text “Total”. You don’t need to do this if you don’t want to.
$sth->execute($params);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
Lastly, we execute the prepared statement, passing in $params as built above. PDO will correctly substitute each :country* parameter with a value from this array. We then call the PDO fetchAll() function to get all rows back at once (you may not want to do this - you may want to return a row at a time, depending on memory considerations and the size of your result set). I’ve just dumped the result via var_dump() for simplicity, but you can do with it as you please.
Any questions? 
