If you're stuck, why not just ask the group for help?
Mostly because it's a pretty long explanation and I didn't want to look like an idiot! From the questions and answers I've seen so far, you all talk so far above me that I probably won't understand any answer given. Unlike many of you, I've not had ANY programming background.
Okay here goes: The beginning form is at http://www.katsuey.com/LinkSearch.cfm
It is suppose to have the user select either a category or keywords which are then
suppose to give the results at http://www.katsuey.comLinkSearchResult.cfm
Right now as you will see, the problem is that while it does indeed output the
category, it is not selecting ONLY the records with this CategoryName designation.
So lets talk first about my DB then the query.
MySQL DB has 4 tables, only 3 come into play with this query.
TABLES:
links - FIELDS: LinkID (primary key), SiteName, SiteLink, SiteDescription, KeyWords (index)
categories - FIELDS: CategoryID (primary key), CategoryName
catlinks - FIELDS: CatLinkID (primary), LinkID (foreign key), CategoryID (foreign key) CategoryName (not using right now, planned as a foreign key but don't think it's needed)
Okay, the Query:
<CFSET WhereClause = " 0=0 ">
<cfif Form.CategoryOperator EQ "EQUALS">
<cfset WhereClause = WhereClause & " AND categories.CategoryName = '" & #Trim(form.CategoryValue)# & "'" >
<cfelseif Form.KeyWordsOperator EQ "IN">
<cfset WhereClause = WhereClause & " AND (links.KeyWords) = '" & #Trim(form.KeyWordsValue)# & "'" >
</CFIF>
<cfquery NAME="rsLinks" datasource="#REQUEST.DataSource#" >
SELECT links.LinkID, links.SiteName, catlinks.LinkID, catlinks.CategoryID, categories.CategoryID, categories.CategoryName, links.SiteLink, links.SiteDescription, links.KeyWords, catlinks.CatLinkID
FROM catlinks, categories, links
WHERE #PreserveSingleQuotes(WhereClause)# AND links.LinkID = catlinks.LinkID ORDER BY CategoryName ASC
</cfquery>
I put in the Trim() because CategoryName and KeyWords are both Char fields and I believe the spacing
might be causing a problem - though you can see that the query does read the CategoryName correctly
it's just that for some reason the output was listing the site information over and over (10 or more times in
some cases) and so I added the end of the WHERE clause: AND links.LinkID = catlinks.LinkID ORDER BY CategoryName ASC
</cfquery> (this not loaded to the server) which prevents that but we are still getting ALL the records in the db instead of
just the ones with the correct CategoryName.
Aren't you glad you asked?
Peggie Brown
((( �.-���- ^~~ ^
( {>�Y�<}
\ )_ `~-~/�
\,,/,,/ \,,,�,,�
Brown Holdings LLC Group
http://www.BusinessSights.com Vision for your Business
http://www.RealGems.biz at Real Low Prices
913.727.1794
========================================================Kansas City ColdFusion User Group's website & listserv is hosted through the generous support of Clickdoug.com To send email to the list, email [EMAIL PROTECTED] To unsubscribe, send an email to [EMAIL PROTECTED] with the following in the BODY of the message: unsubscribe kcfusion For hosting solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. =====================================================
