CODEWITHSUNDEEP
phpsqlcountduplicates
Adam Tal
Adam Tal

Reputation: 921

Count number of identical rows in MySQL with PHP

I have a table in MySql with a list of keywords. Each keyword was stored in a new row once it was entered by a user on this site.

I have the following query in PHP:

SELECT * FROM keywords GROUP BY query

Which gets all the keywords from MySql, and only shows one of each incase of duplicate keywords. So the output is something like:

Dog
Cat
Lion
Fong

When I'm using $update['query'];

But I'd like to count how many times each keyword appears in the database, so the output would be, for example:

Dog  (2)
Cat  (3)
Lion (1)
Fong (1)

And I'm trying to figure out what the SQL query should be, and how to print it using PHP.

Upvotes: 1

Views: 2074

Answers (6)

Chandu
Chandu

Reputation: 82903

Try this query:

SELECT query, COUNT(1) AS rpt_count FROM keywords GROUP BY query

and in PHP you would access the columns using $update['query'] and $update['rpt_count']

Upvotes: 3

Brad
Brad

Reputation: 163232

SELECT keyword, COUNT(*) FROM keywords GROUP BY keyword;

Upvotes: 1

calumbrodie
calumbrodie

Reputation: 4792

SELECT query, COUNT(query) FROM keywords GROUP BY query

Upvotes: 1

genesis
genesis

Reputation: 50976

SELECT *, count(1) FROM keywords GROUP BY query

Upvotes: 1

lxa
lxa

Reputation: 3332

Use SELECT *, COUNT(*) AS cnt FROM keywords GROUP BY query.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

SELECT *, count(*) as cnt FROM keywords GROUP BY query

Upvotes: 1

Related Questions