SkillShare - A place to discuss Web Standards and Web Design topics
Categories
Vanilla 1.1.9 is a product of Lussumo. More Information: Documentation, Community Support.
- Friends
-
Hi,
Does anyone know the query syntax if I wanted to perform the following:
CID BID
1 - 45
2 - 67
3 - 45
4 - 12
5 - 67
6 - 45
BID CIDCount
12 - 1
45 - 3
67 - 2
Any help would be great.
Cheers -
Try:
SELECT `bid`, COUNT(`cid`) FROM `table` GROUP BY `bid` ORDER BY `bid`;I think that's all you need.
-
OK that's great.
So the CID is for comment ID's and BID is for blog ID's. How could I append the this information ie the number of comments to the the end of my blog query.
So something like
BID Blog CIDCount
12 - Blog 1 - 1
45 - Blog 2 - 3
67 - Blog 3 - 2
??? -
Okay… I'm going to assume a few things:
- Your blogs table is named "blogs", and it has columns named "id" and "name".
- Your comments table is named "comments", and it has columns named "id" and "blog_id".
I apologize if I've renamed columns in a way you don't like, but most of my work is done in Rails and I prefer their naming conventions. Anyway, the query you asked for above is:
SELECT `blogs`.`id`, `blogs`.`name`, COUNT(`comments`.`id`)
FROM `blogs` INNER JOIN `comments` ON `blogs`.`id` = `comments`.`blog_id`
GROUP BY `blogs`.`id`
ORDER BY `blogs`.`id`;Hopefully that helps&8230;
-
OK, that partly does what I need... the one problem is that it doesn't account for no comments agains a blog.
I need the list to show 0 for no comments agains a blog?
Is this possible? -
Try changing the "INNER JOIN" to a "LEFT JOIN".
-
Are you trying to build your own CMS or something?
-
Indeed.
Left Join brings back a count for each record, but it gives a count of 1 instead of 0... -
"LEFT JOIN" is the correct query for what you're looking for. I tested the query and it returned the expected result every time.
If I may ask… why are you developing your own CMS? I don't mean to sound condescending, but if you're not able to set up these queries, I think you're going to have a hard time successfully pulling off an entire CMS.
By the way… I'm available for hire if you're looking for a partner.
-
Thanks for your help but I have managed to get it working now through some advice from a friend. As for being a partner, this is a personal project at the minute.
1 to 10 of 10