Counting no. of articles in section in category

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Counting no. of articles in section in category

Post by blade_922 »

Ok im havin some trouble. Here is my objective.
First i managed to display all the category names that are in sectionid 11 in a list.

Now i want to count the number of articles in the particular category in section 11.

my article table is: mos_content
section field is: sectionid
category field: catid

I want to display it like

Category name (no.of.articles.in.category)
Category name (no.of.articles.in.category)
Category name (no.of.articles.in.category)
Category name (no.of.articles.in.category)
Category name (no.of.articles.in.category)

I managed to display the total number of articles in section 11 but not within each category. Here is my code. Im doing the count and category list seperate as im kinda new to this. so can ya help me out :)

Code: Select all

<?php





$count=mysql_query("SELECT COUNT(*) FROM mos_content WHERE sectionid='11'");


echo "</br>";
{
echo mysql_result($count,0);

 }
echo "</br>";



$cats=mysql_query("select * from mos_categories WHERE section='11' ORDER BY `title` ASC") or die(mysql_error());
while ($donnee = mysql_fetch_array($cats))
{ $title= $donnee['title'];  

echo $title;
echo "</br>";

 }



?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT COUNT(`sectionid`) FROM `mos_content` GROUP BY `sectionid`;

SELECT COUNT(`catid`), `sectionid`, `catname` FROM `mos_content` GROUP BY `sectionid`, `catid`;
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

uuuhh which count thingy should i use in the above post? and see then second one it says 'catname' uh i dont think there is a field called 'catname' in mos_content. theres a table called mos_categories and another called mos_sections

im confused now lol
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

SELECT COUNT(`catid`) AS cat_count FROM `mos_content` WHERE `sectionid` = 11;
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

hhmm that just gives me the same number all the time.

maybe i wasnt clear enough.

Ok i'll put it this way, it might be easier. I want to count the total number of articles with the same 'catid' in the mos_content table and all those articles will have 'sectionid' 11.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

blade_922 wrote:uuuhh which count thingy should i use in the above post? and see then second one it says 'catname' uh i dont think there is a field called 'catname' in mos_content. theres a table called mos_categories and another called mos_sections

im confused now lol
You didn't specify the structures of any other tables, so it's difficult to give you an exact, totally working query.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

my bad man.

Ok here is the table structures and fields u probably need to know.

table: mos_content
field: sectionid
field: catid

table: mos_categories
field: name
field: section



and objective is

I want to count the total number of articles with the same 'catid' in the mos_content table and all those articles will have 'sectionid' 11.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT COUNT(`mos_content`.`catid`), `mos_categories`.`name` FROM `mos_content` INNER JOIN `mos_categories` ON `mos_content`.`catid` = `mos_categories`.`catid` GROUP BY `mos_content`.`catid`;
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

EDIT | I liked Feyd's better.
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

ok i think we're almost there. when i use the code feyd gave me it give me a error for this piece of code to display the results of the mysql query

Code: Select all

echo "</br>"; 
{ 
echo mysql_result($count,0); 

 } 
echo "</br>";

error is Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/gtcave/public_html/test/index10.php on line 17


and here is the entire code using feyd's way.

Code: Select all

<?php







$count=mysql_query("SELECT COUNT(`mos_content`.`catid`), `mos_categories`.`name` FROM `mos_content` INNER JOIN `mos_categories` ON `mos_content`.`catid` = `mos_categories`.`catid` GROUP BY `mos_content`.`catid`;");


$cats=mysql_query("select * from mos_categories WHERE section='11' ORDER BY `title` ASC") or die(mysql_error());
while ($donnee = mysql_fetch_array($cats))
{ $title= $donnee['title'];  

echo $title;
echo "</br>";


echo "</br>"; 
{ 
echo mysql_result($count,0); 

 } 
echo "</br>";


 }



?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the sql may need some adjustment, I can't tell as I don't have access to your database (nor do I want it.)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Try error checking the thing that is giving you errors...

Code: Select all

$count=mysql_query("SELECT COUNT(`mos_content`.`catid`), `mos_categories`.`name` FROM `mos_content` INNER JOIN `mos_categories` ON `mos_content`.`catid` = `mos_categories`.`catid` GROUP BY `mos_content`.`catid`;") or die('This query has some problems: ' . mysql_error());
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

This query has some problems: Unknown column 'mos_categories.catid' in 'on clause'

whats that mean?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

It means that you are trying to join on a column that doesn't exist in the table. Does the 'mos_categories' table have a field called 'catid'?
blade_922
Forum Contributor
Posts: 132
Joined: Wed Jul 12, 2006 4:57 pm

Post by blade_922 »

uh no, i said in the above post
table: mos_content
field: sectionid
field: catid

table: mos_categories
field: name
field: section
do you know what it should say instead of that?


hhmm i think this is being thought about to much as if its complex. ok the mos_content table has all my articles etc and in there there are fields for them all, including sectionid and catid. these fields are just numbers. If an article is in the same category as another article they have the same catid.

Now i want to count the amount of articles that have the same catid. Then once i can get that displayed i want to filter that list to show only categorys that have sectionid as 11.
Post Reply