Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Counting Query

Status
Not open for further replies.
ITGL72

ITGL72

MIS
Jul 2, 2001
105
0
0
US
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)

If you go to you will see the template I am working on. You'll notice the (0) 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. 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.

Thanks!

George




<CFQUERY NAME=&quot;CATZ&quot; DATASOURCE=&quot;GLKB&quot;>
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>

<CFQUERY NAME=&quot;NUMZ&quot; DATASOURCE=&quot;GLKB&quot;>
Select CONTENTID, CATEGORY, SUBJECT, CONTENT, DATE, EDIT
From KNOWLEDGEBASE
</CFQUERY>





<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV=&quot;Content-Type&quot; CONTENT=&quot;text/html; charset=iso-8859-1&quot;>
<LINK REL=&quot;stylesheet&quot; HREF=&quot;glcss.css&quot; TYPE=&quot;TEXT/CSS&quot;>
</HEAD>






<TABLE WIDTH=&quot;525&quot; CELLSPACING=&quot;3&quot; CELLPADDING=&quot;3&quot;>
<TR>
<CFOUTPUT QUERY=&quot;CATZ&quot;>
<TD><A HREF=&quot;catpage.cfm?CATID=#CATZ.CATID#&quot;>#CATEGORY#</A> (0)<BR>
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>








</BODY>
</HTML>
 
Sort by date Sort by votes
A custom tag will do this.. syntax would be

<CF_GetCatzChild which=&quot;#Catz.CatzID#&quot;>(#NumIs#)

And the tag code would be something like:

<CFQUERY NAME=&quot;GetNumz&quot; DATASOURCE=&quot;GLKB&quot;>
Select CONTENTID
From KNOWLEDGEBASE
WHERE Category=#Attributes.Which#
</CFQUERY>

<CFSET Caller.NumIs=GetNumz.Recordcount>

Thanks,
Tony Hicks
 
Upvote 0 Downvote
Did you name that custom tag that, or do you have the name of the actual custom tag I can go look for and install?

I apologize for the example not being clear enough for me. :-(
 
Upvote 0 Downvote
It's just one I made up.. but it should do the trick, maybe with modifications.
 
Upvote 0 Downvote
You might try this:

<CFQUERY NAME=&quot;CATZ&quot; DATASOURCE=&quot;GLKB&quot;>
Select CATID, CATEGORY
From CATEGORIES
Order By CATEGORY
</CFQUERY>

<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV=&quot;Content-Type&quot; CONTENT=&quot;text/html; charset=iso-8859-1&quot;>
<LINK REL=&quot;stylesheet&quot; HREF=&quot;glcss.css&quot; TYPE=&quot;TEXT/CSS&quot;>
</HEAD>

<TABLE WIDTH=&quot;525&quot; CELLSPACING=&quot;3&quot; CELLPADDING=&quot;3&quot;>
<cfloop query =&quot;CATZ&quot;>
<CFQUERY NAME=&quot;NUMZ&quot; DATASOURCE=&quot;GLKB&quot;>
Select count(CONTENTID) AS CatCount
From KNOWLEDGEBASE
WHERE CATEGORY = '#CATEGORY#'
</CFQUERY>
<CFOUTPUT>
<TR>
<TD><A HREF=&quot;catpage.cfm?CATID=#CATZ.CATID#&quot;>#CATEGORY#</A> (#numz.catcount#)<BR>
</TD>
</TR></cfoutput>
</cfloop>
</TABLE>
 
Upvote 0 Downvote
I ended up getting it to work. Someone assisted me on another forum. The code I will be using looks like this:

<CFQUERY NAME=&quot;CATZ&quot; DATASOURCE=&quot;GLKB&quot;>
Select CATEGORIES.CATID, CATEGORIES.CATEGORY, COUNT(KNOWLEDGEBASE.CATEGORY) As CategoryCount
From KnowledgeBase RIGHT JOIN CATEGORIES ON KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID
GROUP BY CATEGORIES.CATID,CATEGORIES.CATEGORY
Order By CATEGORIES.CATEGORY
</CFQUERY>

<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV=&quot;Content-Type&quot; CONTENT=&quot;text/html; charset=iso-8859-1&quot;>
<LINK REL=&quot;stylesheet&quot; HREF=&quot;glcss.css&quot; TYPE=&quot;TEXT/CSS&quot;>
</HEAD>

<TABLE WIDTH=&quot;525&quot; CELLSPACING=&quot;3&quot; CELLPADDING=&quot;3&quot;>
<TR>
<CFOUTPUT QUERY=&quot;CATZ&quot;>
<TD><A HREF=&quot;catpage.cfm?CATID=#CATZ.CATID#&quot;>#CATEGORY#</A> (#CategoryCount#)<BR>
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>


</BODY>
</HTML>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
119
jdhilljr
jdhilljr
Ceilicat
  • Locked
  • Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
54
OneTrickPony
OneTrickPony
redoakhg
  • Locked
  • Question
SQL Query Help 1
Replies
5
Views
66
redoakhg
redoakhg
mptwoadmin
  • Locked
  • Question
Update form from query preload?
Replies
1
Views
53
LyndonOHRC
LyndonOHRC
malibu65k
  • Locked
  • Question
Show a button on a page based on query results
Replies
1
Views
52
okiiyama
okiiyama

Part and Inventory Search

Sponsor

Back
Top