PHP/MySQL - How to count number of occurencies

Question

Here is the structure of my MySQL table.

I have many results. Many of them have same Brand for example many of them have Brand - Orrange, many of them have Brand - Oddiboss and Brand - Guma.

So my question is how can i display them like this:

I think i have to use while loop.

$r = mysql_query("SELECT * FROM `products`");

while($rowi = mysql_fetch_array($r))
        {

        $id = $rowi['id'];
        $Title = $rowi['Title'];
        $Brand = $rowi['Brand'];
        }

So with this MySQL quesry i can fetch all the results but i can not group them into 3 results displaying how many occurencies everyone have.

I hope you understand what i need.

And you really shouldn't be using the mysql_ functions – xkcd149 Dec 18 '15 at 20:02 — xkcd149, Dec 18 '15 at 20:02

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

This query should do it:

SELECT Brand, COUNT(*) AS count 
FROM products 
GROUP BY Brand

It should produce these results:

+----------+-------+
| Brand    | count |
+----------+-------+
| Orange   | 23    |
| Oddiboss | 342   |
| Guma     | 2     |
+----------+-------+

While you really shouldn't be using the MySQL extension for accessing the database, here is how you would fetch these results:

$r = mysql_query("SELECT Brand, COUNT(*) AS count FROM `products` GROUP BY Brand");   
while($row = mysql_fetch_array($r))
{
    echo $row['Brand'] . ' ' . $row['count'] . "<br />\n";
}

edited May 23 '17 at 11:52

Community

1
1

answered Dec 18 '15 at 19:52

nickb

59,313
13
108
143

Add please the echo result to your answer. – Venelin Dec 18 '15 at 19:53
I need the `echo` result so i can see it. – Venelin Dec 18 '15 at 19:54
@TonyStark - Do you not know how to run the query? – nickb Dec 18 '15 at 19:55
I do not understand your question – Venelin Dec 18 '15 at 19:58
How can i display the results, should i use while loop ? – Venelin Dec 18 '15 at 19:58
@TonyStark - How are you accessing your database? Which PHP extension, MySQL, MySQLi, PDO? – nickb Dec 18 '15 at 19:59
I use MySQL for accessing. – Venelin Dec 18 '15 at 20:00
@TonyStark - I updated my answer. Please note that MySQL is deprecated and you really should be using a newer extension to access your database. – nickb Dec 18 '15 at 20:02
Why i should not use MySQL extension for accessing the database ? – Venelin Dec 18 '15 at 20:03
@TonyStark - I added a link in my answer to explain why you shouldn't be. – nickb Dec 18 '15 at 20:04
How can i add the `WHERE` statement, for example `WHERE Category='$Cat'` ? – Venelin Dec 19 '15 at 16:45

score 0 · Answer 2 · answered Dec 18 '15 at 19:54

0

You should group by brand column as below :

Select Brand, COUNT(*) AS count FROM products group by Brand

answered Dec 18 '15 at 19:54

AnkiiG

3,468
1
17
28

And how can i display them ? – Venelin Dec 18 '15 at 19:54

score -1 · Answer 3 · answered Dec 18 '15 at 20:03

-1

$sql = SELECT Brand, COUNT(*) AS count FROM products GROUP BY Brand
[...]
echo $row['COUNT(*)'];

answered Dec 18 '15 at 20:03

Mo Mononoke

175
1
11

PHP/MySQL - How to count number of occurencies

3 Answers3