unkn0wnx unkn0wnx - 3 months ago 12
MySQL Question

PHP MYSQL: Return rows for each unique entry

I have managed to set up a PHP script that records visitors on my website, with geolocation.
I am trying to edit the code to result how many hits I have for each country, like US: 14; GB: 2; and so on...
This is the code that returns "132" on each column, so no unique hits for each country.

$squery = 'SELECT `gcountry`, `dtime` FROM `allowed`';
$ress = mysqli_query($conn, $squery);
echo '<style>.tables {margin-left: 6cm;}</style>';
echo '<table border="1" class="tables">';
echo'<th><font color="white">Country</font></th><th><font color="white">Date / Time</font></th><font color="white">Hits</font></th>';

while($data = mysqli_fetch_array($ress))
{
echo '<tr>';
echo '<td><font color="white">&nbsp;'.$data['gcountry'].'&nbsp;</font></td><td><font color="white">'.$data['dtime'].'&nbsp;</font></td><td><font color="white">'.mysqli_affected_rows($conn).'&nbsp;</font></td>';
echo '</tr>';
}


Edited the post, so...
Here is the code, and attaching table entries:

$squery = 'SELECT gcountry,COUNT(*) FROM allowed GROUP BY gcountry';
$ress = mysqli_query($conn, $squery);

echo '<style>.tables {margin-left: 6cm;}</style>';
echo '<table border="1" class="tables">'; echo'<th><font color="white">Country</font></th><font color="white">Hits</font></th>';

while ($data = mysqli_fetch_array($ress)) {
echo'<tr>';
echo '<td><font color="white">&nbsp;'.$data['gcountry'].'&nbsp;</font></td><td><td><font color="white">'.mysqli_affected_rows($conn).'&nbsp;</font></td>';
echo'</tr>';
}





Table content:

IP | UserAgent | GCOUNTRY | GCITY | DTIME
xxx.xxx.xxx.xxx |Mozilla Firefox .... | GB | London | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RU | Moscow | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | GB | London | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | GB | London | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | US | Lombard | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | US | San Antonio | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Constanta | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Bucharest | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Iasi | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | NL | Amsterdam | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | US | Fort Lauderdale | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Constanta | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Constanta | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RO | Bucharest | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | RU | Moscow | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | US | Irving | 21-08-2016
xxx.xxx.xxx.xxx |Mozilla Firefox .... | US | Anaheim | 21-08-2016

Answer

Have a look at the COUNT function, and how it can be used with GROUP BY:

Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+