Page 1 of 2
Counting no. of articles in section in category
Posted: Wed Aug 02, 2006 10:53 am
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>";
}
?>
Posted: Wed Aug 02, 2006 11:04 am
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`;
Posted: Wed Aug 02, 2006 11:16 am
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
Posted: Wed Aug 02, 2006 11:28 am
by RobertGonzalez
Code: Select all
SELECT COUNT(`catid`) AS cat_count FROM `mos_content` WHERE `sectionid` = 11;
Posted: Wed Aug 02, 2006 11:36 am
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.
Posted: Wed Aug 02, 2006 11:47 am
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.
Posted: Wed Aug 02, 2006 11:54 am
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.
Posted: Wed Aug 02, 2006 12:02 pm
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`;
Posted: Wed Aug 02, 2006 12:03 pm
by RobertGonzalez
EDIT | I liked Feyd's better.
Posted: Wed Aug 02, 2006 12:19 pm
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>";
}
?>
Posted: Wed Aug 02, 2006 12:39 pm
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.)
Posted: Wed Aug 02, 2006 12:53 pm
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());
Posted: Wed Aug 02, 2006 1:05 pm
by blade_922
This query has some problems: Unknown column 'mos_categories.catid' in 'on clause'
whats that mean?
Posted: Wed Aug 02, 2006 1:07 pm
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'?
Posted: Wed Aug 02, 2006 1:12 pm
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.