Retrieving count of column mySQL PHP

Question

Hey guys I have a mysql table called interests with 4 columns. interestID, name, categoryID interest_desc and date. the categoryID column is linked to a seperate table. How would I use a mysql query that checked how many interests are in a certain category?

Im guessing i use some sort of count() query?

Thanks guys

Update -

$count_query_v1 = "SELECT categoryID, COUNT(*) AS total FROM interests GROUP by categoryID; ";     $answer = mysql_query($count_query_v1) or die(mysql_error()); echo $answer;

Getting closer but still not perfect, i want to echo out the categoryID with the most interestID's

Pradeep Singh · Accepted Answer · 2011-12-14 06:55:34Z

2

select category_name, count(*) as total
from interests i left join category c on c.category_id = i.category_id
group by i.category_id;

answered Dec 14, 2011 at 6:55

Pradeep Singh

3,6323 gold badges33 silver badges42 bronze badges

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

Comments

ajreal · Accepted Answer · 2011-12-14 06:33:25Z

0

count + group by,
assuming interestID is the unique primary key,
and each interest is tied to single category (as what you have shown)

select categoryID, count(*) as total
from interests
group by categoryID;

// the above example is a simple group by ID without using inner join

output :-

categoryID, total

answered Dec 14, 2011 at 6:33

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

1 Comment

DIM3NSION Over a year ago

Trying to print out the categoryID and the total interests $count_query_v1 = "SELECT categoryID, COUNT(*) AS total FROM interests GROUP by categoryID; "; $answer = mysql_query($count_query_v1) or die(mysql_error()); echo $answer;

Jeremy D · Accepted Answer · 2011-12-14 06:35:33Z

0

SELECT COUNT(interestID) FROM interests WHERE categoryID = 'yourvalue';

SELECT COUNT(interestID), categoryID FROM interests GROUP BY categoryID;

answered Dec 14, 2011 at 6:35

Jeremy D

4,8551 gold badge34 silver badges39 bronze badges

4 Comments

DIM3NSION Over a year ago

how would i go about echoing the category with the most interestsID's

Jeremy D Over a year ago

You want to echo : the number,the name of the category, with categoryID belonging to another table let's say category?

DIM3NSION Over a year ago

I want to get the category with the most interestID's in it

Jeremy D Over a year ago

SELECT MAX(COUNT(interestID)), categoryID FROM interests GROUP BY categoryID;

Learner_bangalore · Accepted Answer · 2011-12-14 06:34:38Z

-1

Since you are using the insert query each query will insert one record, so just count the number of insert queries you run by using a counter varialble.

answered Dec 14, 2011 at 6:34

Learner_bangalore

344 bronze badges

Collectives™ on Stack Overflow

Retrieving count of column mySQL PHP

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related