gplemos
asked on
Counting Query
I'm working on a web based IT knowledbase type app for my personal use. I'm trying to accomplish something I have seen on other websites. I have a DB with two tables. One table has all the different categories and their category id, and the other table has a the actual content, category ID, etc. (Look at the attached code below and look at the CFQUERY pulling out of the KNOWLEDGEBASE table or download a copy of this DB here: http://www.georgelemos.com/glkb.zip)
If you go to http://www.georgelemos.com/categories.cfm you will see the template I am working on. You'll notice the (4) next to each category. What I want to do, is query the KNOWLEDGEBASE table and count how many articles I have stored in the DB for each CATEGORY. Then next to each category have a number displaying how many articles for that CATEGORY are stored in the DB.
I have tried a few different ways but I really came up with nothing that works. Right now all its doing is displaying how many entries are in cetegory 1 (which are 4) I am assuming there's some type of CFLOOP needed which I am really awful with.
If someone has a piece of code available that could help me out I would greatly appreciate it. The code below is what I have running on the page URL listed above.
Thanks!
<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, (SELECT Count(*)
FROM KNOWLEDGEBASE where KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID) AS CategoryCount
FROM CATEGORIES;
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>
<TABLE WIDTH="525" CELLSPACING="3" CELLPADDING="3">
<TR>
<CFOUTPUT QUERY="CATZ">
<TD><A HREF="catpage.cfm?CATID=#C
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>
If you go to http://www.georgelemos.com/categories.cfm you will see the template I am working on. You'll notice the (4) next to each category. What I want to do, is query the KNOWLEDGEBASE table and count how many articles I have stored in the DB for each CATEGORY. Then next to each category have a number displaying how many articles for that CATEGORY are stored in the DB.
I have tried a few different ways but I really came up with nothing that works. Right now all its doing is displaying how many entries are in cetegory 1 (which are 4) I am assuming there's some type of CFLOOP needed which I am really awful with.
If someone has a piece of code available that could help me out I would greatly appreciate it. The code below is what I have running on the page URL listed above.
Thanks!
<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, (SELECT Count(*)
FROM KNOWLEDGEBASE where KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID) AS CategoryCount
FROM CATEGORIES;
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>
<TABLE WIDTH="525" CELLSPACING="3" CELLPADDING="3">
<TR>
<CFOUTPUT QUERY="CATZ">
<TD><A HREF="catpage.cfm?CATID=#C
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>
oops forgot the ending </cfoutput>:
<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
</cfoutput>
<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
</cfoutput>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>Looping over a query, though, can be "expensive" overhead
true but it really does depend on the data set.
If the categories is under like 500 .. its probably
fine performance wise.
CJ
true but it really does depend on the data set.
If the categories is under like 500 .. its probably
fine performance wise.
CJ
ASKER
Using the code below on URL:
http://www.georgelemos.com/categories2.cfm
I get the error:
"ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CATEGORY' as part of an aggregate function.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:1) to (8:39)."
Any suggestions?
<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>
<cfloop query="CATZ">
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, COUNT(*) AS CategoryCount
FROM KNOWLEDGEBASE, CATEGORIES where KNOWLEDGEBASE.CATEGORY = CATZ.CATID
AND CATEGORIES.CATID = KNOWLEDGEBASE.CATEGORY
</CFQUERY>
</cfloop>
<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>
<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
</cfoutput>
</BODY>
</HTML>
I think you need a GROUP BY clause in the query (see my comment from above about doing it all in one query -- same idea)
ASKER
That did the trick. Thank you to both of you.
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>
<cfloop query="CATZ">
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, COUNT(*) AS CategoryCount
FROM KNOWLEDGEBASE, CATEGORIES where KNOWLEDGEBASE.CATEGORY = CATZ.CATID
AND CATEGORIES.CATID = KNOWLEDGEBASE.CATEGORY
</CFQUERY>
</cfloop>
should work.
Just use the query numz now to display the stuff like:
<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
CJ