I'm creating a blog and I want to count the comments viewers have left.
So far I've created a database with two tables, one for the articles, and the other for the comments.
Here is the structure of my database:
Databasename: testBlogDB
Table One Name: tblmainindex
rowID
articleTitle
article
Table Two Name: tblcomments
commentID
comments
The software I am using is Dreamweaver 8. I need to know how to set up the query as far as what to SELECT, where FROM, and what ever else. When I tried to do this earlier I was counting all the comments in the database by #testBlogDB.RecordCount#, which is wrong. I only want to count comments related to the article, so which rows do I join?
Also, I need clarification on the %26lt;cfoutput%26gt;%26lt;/cfoutput%26gt; settings.
Please, please do not refer me to Ben Forta's blog application. I purposely wanted to do this blog from scratch. How else am I supposed to learn? I appreciate all of your help, thank you so much.
Hope to hear from
Coldfusion: Counting comments. Count / RecordCount?
Pre-answer comment: You're absolutely right about learning! There is no better way to learn than to take on writing something that has already been done. You have well-defined goals by looking at what the existing product does, and you can get plenty of help because people are already familiar with how such a product works.
First question regarding SQL:
You should name your RowID in tblMainIndex something like ArticleID.
Your second table ("tblComments") needs to have an ArticleID field that matches back to the article the comment was made on. So your data structure would look like this:
tblMainIndex:
1 | AnArticle | contents
2 | AnotherArticle | contents of that one
tblComments:
1 | 1 | comment on article 1
1 | 2 | another on article 1
2 | 3 | comment on article 2
2 | 4 | another comment on article 2
Then your query to select comments would look like this:
SELECT CommentID, Comments FROM tblComments LEFT JOIN tblMainIndex ON tblComments.ArticleID = tblMainIndex.ArticleID WHERE ArticleID=@ArticleID
In your CF code, you need to supply the value of the @ArticleID parameter to be the ID of the article you're currently referring to.
To get a count, just replace the selected columns in the above query with COUNT(*) AS CommentCount.
Regarding the cfoutput, unfortunately my CF is pretty rusty.
If you are trying to get a comment count to output, just do:
%26lt;cfquery (your query)...%26gt;
%26lt;cfoutput query="queryName"%26gt;
%26lt;pre%26gt;#CommentCount#%26lt;/pre%26gt;
%26lt;/cfoutput%26gt;
Since CommentCount is the name of the column you want from that query, it will replace any instance of #columnname# inside the %26lt;pre%26gt; markup with that value.
Keep in mind that doing a separate query just to get the count is very bad for performance; this is a learning experience so that's OK, but after you get it to work, you should try going back and rewriting to optimize for performance. Always minimize the number of separate queries you make to any external system, like SQL.
Coldfusion: Counting comments. Count / RecordCount?
Look up parameterized queries in ColdFusion. It allows you to specify "variables" in your query that you can define at runtime, such as getting the articleID from the querystring (article.cfm?aid=12) for example. Report It
No comments:
Post a Comment