CODEWITHSUNDEEP
phpmysqldatabasecount
DIM3NSION
DIM3NSION

Reputation: 1761

Retrieving count of column mySQL PHP

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

Upvotes: 0

Views: 836

Answers (4)

Pradeep Singh
Pradeep Singh

Reputation: 3634

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;

Upvotes: 2

Jeremy D
Jeremy D

Reputation: 4855

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

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

Upvotes: 0

Learner_bangalore
Learner_bangalore

Reputation: 34

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.

Upvotes: -1

ajreal
ajreal

Reputation: 47321

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

Upvotes: 0

Related Questions