How to count MySQL array values in PHP?

Question

How to count MySQL array values? For example, the below code

$get = mysqli_query("SELECT * FROM clicks WHERE uid='$id'");
while($comp = mysqli_fetch_array($db, $get)) {
$country = $comp['country'];
echo "$country<br>";
}

will give output country names in the table country as

India
India
Pakistan
India
United States
Japan
United States
United States
India

I want to count the number of times each country appears in the table and store the values as:

 ['Country', 'Counts'],
 ['India', 4],
 ['Pakistan', 1],
 ['United States', 3],
 ['Japan', 1],

You are looking for group by, a fundamental part of SQL. You should learn the basics of SQL if you want to use databases at all effectively. — Gordon Linoff
– Gordon Linoff, Commented Jul 26, 2015 at 13:51

Samuel Cook · Accepted Answer · 2015-07-26 13:51:35Z

2

Your SQL should read as

SELECT *,COUNT(*) as `count` FROM clicks WHERE uid='$id' GROUP BY `country`

Then you should be able to call the count paramenter:

while($comp=mysql_fetch_array($get)) {
    echo $comp['country'].': '.$comp['count'];
}

Side note: MySQL has been depricated. You should look to switch to MySQLi or PDO.

answered Jul 26, 2015 at 13:51

Samuel Cook

16.9k7 gold badges56 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to count MySQL array values in PHP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related