You just want a list of categories, and number of lectures belonging to
each category, right? That would be something like this, you should only
need to query the db once to do that.
<cfquery>
select category, url,count(lecturename.primarykey) as theCount from
categories c, lectures l
where c.foreignkey=l.primarykey
group by category,url
</cfquery>
[email protected] on Monday, December 11, 2006 at 9:10 AM -0800
wrote:
>I have two tables in specific in my database. The first one has the
>categories of lectures that are offerred. The second table has the list
>of lectures under each category.
>
>On my lecture page, I am need to call both tables to get the category
>titles (first table) as well as the number of lectures available under
>each category (second table). I am almost there, here is my code thus
>far:
>
>
>
><div align="center">
> <table width="600" border="0">
> <tr>
> <th colspan="2"><div align="center">CME Lectures</div></th>
> </tr>
> <tr>
> <td width="500" nowrap><div align="left">Category</div></td>
> <td nowrap><div align="center">Available</div><br /></td>
> </tr>
> <cfoutput query="qGetLectureCategories" >
> <tr>
> <td><div align="left"><a
>href="#qGetLectureCategories.URL#">#qGetLectureCategories.Category#</a></div></td>
> <td><div align="center">
> <cfif qGetLectureCategories.Category IS "All">
> <cfquery name="qGetCategoryCountAll" datasource="sonocme">
> SELECT primaryKey, category, lectureName, URL
> FROM lectureNames
> </cfquery>
> <cfoutput>#qGetCategoryCountAll.recordCount#</cfoutput>
> <cfelse>
> <cfquery name="qGetCategoryCount" datasource="sonocme">
> SELECT primaryKey, category, lectureName, URL
> FROM lectureNames
> WHERE category='#qGetLectureCategories.Category#'
> </cfquery>
> <cfoutput>#qGetCategoryCount.recordCount#</cfoutput>
> </cfif>
> </div></td>
> </tr>
> </cfoutput>
> </table>
></div>
>
>
>
>The resulting webpage looks like this:
>
>
>
>
>CME Lectures
>Category Available
>
>Pediatrics 2222222222
>Abdomen 111111111
>Obstetrics and Gynecology 22222222
>Breast 1111111
>Physics 000000
>Musculoskeletal and Superficial Structures 00000
>Vascular 0000
>Cardiology 000
>Non-Clinical Topics 00
>All 6
>
>
>
>I am getting the correct numbers but as you can see, they are repeating,
>or looping rather, even though I am not running any loops. I am not sure
>how best to proceed. I know that <cfabort> and <cfbreak> will not work
>here.
>
>As always, any help is very much appreciated.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2335
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
