I agree with Ryan. You need to add "AND categories.CategoryID = catlinks.CategoryID" to your WHERE clause.
Randy Ellis Senior Programmer Analyst City of Overland Park -----Original Message----- From: Katsuey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 1:34 PM To: [EMAIL PROTECTED] Subject: [KCFusion] Where I'm Stuck At 01:32 PM 10/22/2003, you wrote: >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. ===================================================== ========================================================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. =====================================================
