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.
<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
Select CATID, CATEGORY
Order By CATEGORY
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, (SELECT Count(*)
FROM KNOWLEDGEBASE where KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID) AS CategoryCount
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
<TABLE WIDTH="525" CELLSPACING="3" CELLPADDING="3">
<CFIF CATZ.CURRENTROW MOD 2 IS 0>