Count number of identical rows in MySQL with PHP

Question

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.

At this moment I'd be happy with the simplest solution, but if youre feeling creative I'd love to learn how to do this with/without being case sensitive. — Adam Tal
– Adam Tal, Commented Jul 11, 2011 at 21:14

Chandu · Accepted Answer · 2011-07-11 21:24:03Z

3

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']

edited Jul 11, 2011 at 21:24

answered Jul 11, 2011 at 21:15

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2011-07-11 21:14:53Z

1

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

answered Jul 11, 2011 at 21:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

2 Comments

blitzmann Over a year ago

Alternatively, use COUNT(*) AS count

Mikael Eriksson Over a year ago

@Ryan - Yes an alias should have been there. Updated.

lxa · Accepted Answer · 2011-07-11 21:15:24Z

1

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

answered Jul 11, 2011 at 21:15

lxa

3,3722 gold badges32 silver badges32 bronze badges

Comments

genesis · Accepted Answer · 2011-07-11 21:15:31Z

1

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

answered Jul 11, 2011 at 21:15

genesis

51.1k20 gold badges99 silver badges127 bronze badges

Comments

calumbrodie · Accepted Answer · 2011-07-11 21:15:35Z

1

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

answered Jul 11, 2011 at 21:15

calumbrodie

4,8025 gold badges39 silver badges64 bronze badges

Comments

Brad · Accepted Answer · 2011-07-11 21:15:42Z

1

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

answered Jul 11, 2011 at 21:15

Brad

164k57 gold badges380 silver badges559 bronze badges

Collectives™ on Stack Overflow

Count number of identical rows in MySQL with PHP

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related