Known Participant

Question

record count

Forum|Forum|18 years ago
November 9, 2007
9 replies
1864 views

I have a list of articles
the are themselves are static pages.
the links to them are in my access db
each article has been given up to 3 tags which puts them into three different categories.
each tag is in a separate field
i.e
tag1 tag2 tag3
STD Cancer Women

I could seem to get the sql to work when i put all the tags in one field (any ideas?)

Now the question

When a user clicks on the category section on my web page and arrives at the next page with a list of the categories I want the number of articles in each category to appear next to the title of the category

example

sexual health [ 37]
womens issues [14]

and so on - increasing as i increase the entries in the db

any ideas?

many thanks

Getting started

This topic has been closed for replies.

Q

quiero_masAuthor

Known Participant

Fantastic Cv - if i can help you out in anyway inthe future please let me know - p.s i will drop you a line when i get everything up n runniong many thanks

C

cf_dev2

Inspiring

Great. Best of luck!

C

cf_dev2

Inspiring

Oh. I think I see the problem. You need to add the CategoryID column to your first query. But verify the column name is correct. I don't know whether you're calling it "ID" or "CategoryID"

<cfquery name="category" datasource="mxAjaxData">
SELECT c.CategoryID, c.CategoryName, COUNT(ac.ArticleID) AS NumberOfArticles
FROM Categories AS c LEFT JOIN ArticleCategories AS ac
ON c.CategoryID = ac.CategoryID
GROUP BY c.CategoryID, c.CategoryName
ORDER BY c.CategoryName
</cfquery>

Q

quiero_masAuthor

Known Participant

by the way I am now using the datasource mxAjaxData
its not the problem because i have had it working with the previous code.

Again thank you for your help

C

cf_dev2

Inspiring

What is it doing or not doing? Are you getting an error, wrong results ..?

Q

quiero_masAuthor

Known Participant

Hi CV - when I run the page I get an error that says (my os is in Japanese) something like the
要素 CATEGORYID は CATEGORY 内で未定義です。
The component CategoryID isn't fully formed with the Category.

Hope this helps

Thanks again CV

Q

quiero_masAuthor

Known Participant

Great Everything is working fine

I rearranged somethings to get the ctegories appearing

I now have my categories page all done many thanks.

I just need one more step.

When the user clicks on the category.
For example Japan
They then are taken to a page with a list of the articles in that category.

Is thins going to be tricky

Many thanks for your help

C

cf_dev2

Inspiring

It should be simple. Add the CategoryID column to your query. When you output the category links pass the CategoryID value as a url parameter. For example


<cfoutput query="category">
<a href="showCategoryArticles.cfm?categoryID=#category.CategoryID#"> #category.CategoryName#</a> 
</cfoutput>

Then on the showCategoryArticles.cfm page, use the categoryID parameter in a query to retrieve the articles

<cfparam name="url.categoryID" default="0">
<cfquery datasource="cart" name="getArticles">
SELECT a.ArticleID, a.ArticleName
FROM Articles AS a INNER JOIN ArticleCategories AS ac
ON a.ArticleID = ac.ArticleID
WHERE ac.CategoryID = <cfqueryparam value="#url.categoryID#" cfsqltype="cf_sql_integer">
ORDER BY a.ArticleName
</cfquery>


<cfoutput query="getArticles">
#ArticleID# #ArticleName# 
</cfoutput>

Q

quiero_masAuthor

Known Participant

Hi gave it a whirl but no joy - have some problems

要素 CATEGORYID は CATEGORY 内で未定義です。

Hope you can help

category.cfm

<cfquery name="category" datasource="mxAjaxData">
SELECT c.CategoryName, COUNT(ac.ArticleID) AS NumberOfArticles
FROM Categories AS c LEFT JOIN ArticleCategories AS ac
ON c.CategoryID = ac.CategoryID
GROUP BY c.CategoryName
ORDER BY c.CategoryName
</cfquery>

<cfoutput query="category">
<a href="showCategoryArticles.cfm?categoryID=#category.CategoryID#"> #category.CategoryName#</a> 
</cfoutput>

//////

CategoryArticles.cfm

<cfparam name="url.categoryID" default="0">
<cfquery datasource="mxAjaxData" name="getArticles">
SELECT a.ArticleID, a.ArticleName
FROM Articles AS a INNER JOIN ArticleCategories AS ac
ON a.ArticleID = ac.ArticleID
WHERE ac.CategoryID = <cfqueryparam value="#url.categoryID#" cfsqltype="cf_sql_integer">
ORDER BY a.ArticleName
</cfquery>


<cfoutput query="getArticles">
#ArticleID# #ArticleName# 
</cfoutput>

Q

quiero_masAuthor

Known Participant

Can't seem to get things working -
I am using an Access database is that going to change things?

the following is what i have so far

(table name) ArticleCategories

ArticleID CategoryID
1 1
2 2
3 3

(table name) Categories

ID CategoryName
1 breast
2 poisening
3 sex

(table name) Articles

ArticleID ArticleName
1 Cancer
2 Children
3 STD

////

<cfquery datasource="cart" name="category">
SELECT c.CategoryName, COUNT(*) AS NumberOfArticles
FROM Categories AS c LEFT JOIN ArticleCategories AS ac
ON c.CategoryID = ac.CategoryID
GROUP BY c.CategoryName
ORDER BY c.CategoryName
</cfquery>

<cfoutput query="category">

#category.CategoryName#
</cfoutput>

C

cf_dev2

Inspiring

Those seem more like "tags" than categories. But the output should work correctly if you do two things:

1) I realized you need to use COUNT(ac.ArticleID) instead of COUNT(*) ... and
2) Add "NumberOfArticles" to the cfoutput.

Also, verify the column names are correct. In your description the Categories table had a column named "ID" but in your JOIN you're calling it "CategoryID".

<cfquery datasource="cart" name="category">
SELECT c.CategoryName, COUNT(ac.ArticleID) AS NumberOfArticles
FROM Categories AS c LEFT JOIN ArticleCategories AS ac
ON c.CategoryID = ac.CategoryID
GROUP BY c.CategoryName
ORDER BY c.CategoryName
</cfquery>

<cfoutput query="category">
#category.CategoryName# [#NumberOfArticles#] 
</cfoutput>

C

cf_dev2

Inspiring

So how does your db relate category -> tags? In other words how do you relate a category (sexual health) to a tag ("STD")

Q

quiero_masAuthor

Known Participant

To be honest Im not so good at DB and coldfusion work.
Currently the only relationship they have is that they are in the same table. So i thought i would follow your advice and put them, the tags, in a separate table them relate them as you suggested.

the tags were created from a text analysis of the particular article.
the category was decided just by knowing the content

really appreciate the help

C

cf_dev2

Inspiring

Usually you would have separate tables for the different objects like category and tag. Then relate them using a 3rd table containing only the ID's, as I mentioned. This avoids duplication of data. It also prevents inconsistencies that can occur when storing text values.

Do "tags" always relate to a single category?

Q

quiero_masAuthor

Known Participant

thanks very much

category = general theme of the articles
tags = main themes in a particular article

H

Hydrowizard

Inspiring

It is difficult to see what you mean by tags

Do you want to make the values into a list or a structure? They already variables I assume or and they custom tags (tag1.cfm, tag2.cfm, tag3.cfm?)

Q

quiero_masAuthor

Known Participant

#correction

I could seem to get the sql to work when i put all the tags in one field (any ideas?)

should be

I couldn't seem to get the sql to work when i put all the tags in one field (any ideas?)

C

cf_dev2

Inspiring

> I couldn't seem to get the sql to work when i put all the tags
> in one field (any ideas?)

That's actually a good thing. Don't store them in a single field. The difficulty you experienced with the sql is one of the main reasons why it should be avoided.

You could make it work with the three tag columns. But usually this type of relationship is represented by a third table. This allows you to associate an article with as many or as few categories as needed without having to add extra columns.

Table: Articles - ArticleID, ArticleName
Table: Categories - CategoryID, CategoryName (ie. STD, Cancer, Women)
Table: ArticleCategory

ArticleID, CategoryID
1, 1 (Category STD)
1, 2 (Category Cancer)
1, 3 (Category Women)
...

The query to get the counts would then become something like

SELECT c.CategoryName, COUNT(*) AS NumberOfArticles
FROM Categories AS c LEFT JOIN ArticleCategories AS ac
ON c.CategoryID = ac.CategoryID
GROUP BY c.CategoryName
ORDER BY c.CategoryName

UPDATE I don't know if "tags" equate to "categories" but I think the 3 table concept still applies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded