<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
<!--- here's the line you were missing ---> and categories.CategoryID = catlinks.CategoryID ORDER BY CategoryName ASC </cfquery> I don't know exactly how much you understand the database and your relationships as to whether this will automatically make sense or if you'll need a full explanation, but in most cases, if you're dealing with N number of tables, then you will have N - 1 number of sometable.somefield = someothertable.somefield parts in your where clause. That general rule can keep you away from having this happen all the time. I can go further into the explanation if you want. Bruce Dunwiddie Ticket Technology P: 866.543.3331 F: 913.451.7832 [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Katsuey Sent: Wednesday, October 22, 2003 12: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. ======================================================
