Solved

Records are being skipped in search results

Posted on 2003-11-22
2
188 Views
Last Modified: 2013-12-24
I have a very simple database search that works fine except for when a field in a row has no value.
I'm searching a SQL database and have coded the search to insert '%' if no value is submited from the form for a particular field. The '%' works fine if there is a value in the database for that particular field but if not the entire row will not be returned even though it contains values for other fields. Any ideas.

My Code:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_Weddings" default="1">
<cfif isdefined("URL.SearchEventType") AND #URL.SearchEventType# IS NOT "">
<cfset EventType = "Event_type LIKE '%#URL.SearchEventType#%'">
<cfelse>
<cfset EventType = "Event_type LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchBrideFirst") AND #URL.SearchBrideFirst# IS NOT "">
<cfset BrideFirst = "AND Bride_first LIKE '%#URL.SearchBrideFirst#%'">
<cfelse>
<cfset BrideFirst = "AND Bride_first LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchBrideLast") AND #URL.SearchBrideLast# IS NOT "">
<cfset BrideLast = "AND Bride_last LIKE '%#URL.SearchBrideLast#%'">
<cfelse>
<cfset BrideLast = "AND Bride_last LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchBrideCity") AND #URL.SearchBrideCity# IS NOT "">
<cfset BrideCity = "AND Bride_City LIKE '%#URL.SearchBrideCity#%'">
<cfelse>
<cfset BrideCity = "AND Bride_City LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchGroomFirst") AND #URL.SearchGroomFirst# IS NOT "">
<cfset GroomFirst = "AND Groom_first LIKE '%#URL.SearchGroomFirst#%'">
<cfelse>
<cfset GroomFirst = "AND Groom_first LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchGroomLast") AND #URL.SearchGroomLast# IS NOT "">
<cfset GroomLast = "AND Groom_last LIKE '%#URL.SearchGroomLast#%'">
<cfelse>
<cfset GroomLast = "AND Groom_last LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchGroomCity") AND #URL.SearchGroomCity# IS NOT "">
<cfset GroomCity = "AND Groom_City LIKE '%#URL.SearchGroomCity#%'">
<cfelse>
<cfset GroomCity = "AND Groom_City LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchWeddingFacility") AND #URL.SearchWeddingFacility# IS NOT "">
<cfset WeddingFacility = "AND Church LIKE '%#URL.SearchWeddingFacility#%'">
<cfelse>
<cfset WeddingFacility = "AND Church LIKE '%'">
</cfif>

<cfif isdefined("URL.SearchWeddingCity") AND #URL.SearchWeddingCity# IS NOT "">
<cfset WeddingCity = "AND Wed_city LIKE '%#URL.SearchWeddingCity#%'">
<cfelse>
<cfset WeddingCity = "AND Wed_city LIKE '%'">
</cfif>

<cfif isdefined("URL.Day") AND #URL.Day# IS NOT "">
<cfset DateDay = "AND Wed_date_day LIKE '%#URL.Day#%'">
<cfelse>
<cfset DateDay = "AND Wed_date_day '%'">
</cfif>

<cfif isdefined("URL.Month") AND #URL.Month# IS NOT "">
<cfset DateMonth = "AND Wed_date_month LIKE '%#URL.Month#%'">
<cfelse>
<cfset DateMonth = "AND Wed_date_month '%'">
</cfif>

<cfif isdefined("URL.Year") AND #URL.Year# IS NOT "">
<cfset DateYear = "AND Wed_date_year LIKE '%#URL.Year#%'">
<cfelse>
<cfset DateYear = "AND Wed_date_year '%'">
</cfif>

<cfquery name="Weddings" datasource="MyData">
SELECT * FROM Wedding WHERE #preservesinglequotes(EventType)#  #preservesinglequotes(BrideFirst)# #preservesinglequotes(BrideLast)# #preservesinglequotes(BrideCity)# #preservesinglequotes(GroomFirst)# #preservesinglequotes(GroomLast)# #preservesinglequotes(GroomCity)# #preservesinglequotes(WeddingFacility)# #preservesinglequotes(WeddingCity)# #preservesinglequotes(DateDay)# #preservesinglequotes(DateMonth)# #preservesinglequotes(DateYear)# ORDER BY Bride_last ASC
</cfquery>




<cfset MaxRows_Weddings=100>
<cfset StartRow_Weddings=Min((PageNum_Weddings-1)*MaxRows_Weddings+1,Max(Weddings.RecordCount,1))>
<cfset EndRow_Weddings=Min(StartRow_Weddings+MaxRows_Weddings-1,Weddings.RecordCount)>
<cfset TotalPages_Weddings=Ceiling(Weddings.RecordCount/MaxRows_Weddings)>
<cfset QueryString_Weddings=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_Weddings,"PageNum_Weddings=","&")>
<cfif tempPos NEQ 0>
  <cfset QueryString_Weddings=ListDeleteAt(QueryString_Weddings,tempPos,"&")>
</cfif>

<link href="inc/style.css" rel="stylesheet" type="text/css">
<cfset number = 0>
<title>Search Results</title><table width="100%" border="0" cellpadding="5" cellspacing="0">
  <tr>
    <td>
    <cfif #Weddings.ID# NEQ "">
          <div align="center"><span class="bodytext" align="center"><center><b>Search Results</b></center></span></div>
          <cfif #URL.SearchEventType# NEQ ""><span class="bodytext" align="left"><b><cfoutput>#URL.SearchEventType#s:</cfoutput></b></span><cfelse><span class="bodytext" align="left"><b>Results:</b></span></cfif>
        <cfoutput query="Weddings" startRow="#StartRow_Weddings#" maxRows="#MaxRows_Weddings#">
            <br>
    <span class="bodytext">
      <cfif #Weddings.Event_type# EQ "Wedding"><a href="wedding_display.cfm?recordID=#Weddings.ID#">     <cfset #number# = #number# + 1>#number# #Weddings.Bride_last# &##8212; #Weddings.Groom_last#</a>
      <cfelse><a href="engagement_display.cfm?recordID=#Weddings.ID#"><cfset #number# = #number# + 1>#number# #Weddings.Bride_last# &##8212; #Weddings.Groom_last#</a>
      </cfif></span>
    </cfoutput>
<cfelse>
  <span class="bodytext">Sorry your search returned no results.</span>
</cfif></td>
  </tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="5" bgcolor="#CEBFD9">
  <tr>
    <td bgcolor="#CCCCCC">
      <table width="50%" border="0" align="center" bgcolor="#CCCCCC">
        <cfoutput>
          <tr>
            <td width="23%" align="center"> <cfif PageNum_Weddings GT 1>
                <a href="#CurrentPage#?PageNum_Weddings=1#QueryString_Weddings#"><img src="img/nav/First.gif" border=0></a>
              </cfif></td>
            <td width="31%" align="center"> <cfif PageNum_Weddings GT 1>
                <a href="#CurrentPage#?PageNum_Weddings=#Max(DecrementValue(PageNum_Weddings),1)##QueryString_Weddings#"><img src="img/nav/Previous.gif" border=0></a>
              </cfif></td>
            <td width="23%" align="center"> <cfif PageNum_Weddings LT TotalPages_Weddings>
                <a href="#CurrentPage#?PageNum_Weddings=#Min(IncrementValue(PageNum_Weddings),TotalPages_Weddings)##QueryString_Weddings#"><img src="img/nav/Next.gif" border=0></a>
              </cfif></td>
            <td width="23%" align="center"> <cfif PageNum_Weddings LT TotalPages_Weddings>
                <a href="#CurrentPage#?PageNum_Weddings=#TotalPages_Weddings##QueryString_Weddings#"><img src="img/nav/Last.gif" border=0></a>
              </cfif></td>
          </tr>
        </cfoutput></table></td>
  </tr>
</table>
0
Comment
Question by:McHack
  • 2
2 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9805804
you really the   ... LIKE '%'  at all, just leave it out of your where clause all together

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_Weddings" default="1">
<cfif isdefined("URL.SearchEventType") AND len(URL.SearchEventType)>
<cfset EventType = "Event_type LIKE '%#URL.SearchEventType#%'">
<cfelse>
<cfset EventType = "">
</cfif>

<cfif isdefined("URL.SearchBrideFirst") AND len(URL.SearchBrideFirst)>
<cfset BrideFirst = "AND Bride_first LIKE '%#URL.SearchBrideFirst#%'">
<cfelse>
<cfset BrideFirst = "">
</cfif>

<cfif isdefined("URL.SearchBrideLast") AND len(URL.SearchBrideLast)>
<cfset BrideLast = "AND Bride_last LIKE '%#URL.SearchBrideLast#%'">
<cfelse>
<cfset BrideLast = "">
</cfif>

<cfif isdefined("URL.SearchBrideCity") AND len(URL.SearchBrideCity)>
<cfset BrideCity = "AND Bride_City LIKE '%#URL.SearchBrideCity#%'">
<cfelse>
<cfset BrideCity = "">
</cfif>

<cfif isdefined("URL.SearchGroomFirst") AND len(URL.SearchGroomFirst)>
<cfset GroomFirst = "AND Groom_first LIKE '%#URL.SearchGroomFirst#%'">
<cfelse>
<cfset GroomFirst = "">
</cfif>

<cfif isdefined("URL.SearchGroomLast") AND len(URL.SearchGroomLast)>
<cfset GroomLast = "AND Groom_last LIKE '%#URL.SearchGroomLast#%'">
<cfelse>
<cfset GroomLast = "">
</cfif>

<cfif isdefined("URL.SearchGroomCity") AND len(URL.SearchGroomCity)>
<cfset GroomCity = "AND Groom_City LIKE '%#URL.SearchGroomCity#%'">
<cfelse>
<cfset GroomCity = "">
</cfif>

<cfif isdefined("URL.SearchWeddingFacility") AND len(URL.SearchWeddingFacility)>
<cfset WeddingFacility = "AND Church LIKE '%#URL.SearchWeddingFacility#%'">
<cfelse>
<cfset WeddingFacility = "">
</cfif>

<cfif isdefined("URL.SearchWeddingCity") AND len(URL.SearchWeddingCity)>
<cfset WeddingCity = "AND Wed_city LIKE '%#URL.SearchWeddingCity#%'">
<cfelse>
<cfset WeddingCity = "">
</cfif>

<cfif isdefined("URL.Day") AND len(URL.Day)>
<cfset DateDay = "AND Wed_date_day LIKE '%#URL.Day#%'">
<cfelse>
<cfset DateDay = "">
</cfif>

<cfif isdefined("URL.Month") AND #URL.Month# IS NOT "">
<cfset DateMonth = "AND Wed_date_month LIKE '%#URL.Month#%'">
<cfelse>
<cfset DateMonth = "AND Wed_date_month '%'">
</cfif>

<cfif isdefined("URL.Year") AND len(URL.Year)>
<cfset DateYear = "AND Wed_date_year LIKE '%#URL.Year#%'">
<cfelse>
<cfset DateYear = "">
</cfif>

<cfquery name="Weddings" datasource="MyData">
SELECT * FROM Wedding
WHERE 1=1
#preservesinglequotes(EventType)#
#preservesinglequotes(BrideFirst)#
#preservesinglequotes(BrideLast)#
#preservesinglequotes(BrideCity)#
#preservesinglequotes(GroomFirst)#
#preservesinglequotes(GroomLast)#
#preservesinglequotes(GroomCity)#
#preservesinglequotes(WeddingFacility)#
#preservesinglequotes(WeddingCity)#
#preservesinglequotes(DateDay)#
#preservesinglequotes(DateMonth)#
#preservesinglequotes(DateYear)#
ORDER BY Bride_last ASC
</cfquery>
0
 
LVL 12

Accepted Solution

by:
jyokum earned 250 total points
ID: 9805806
oops, change this one

<cfif isdefined("URL.SearchEventType") AND len(URL.SearchEventType)>
<cfset EventType = "Event_type LIKE '%#URL.SearchEventType#%'">
<cfelse>
<cfset EventType = "">
</cfif>

to this

<cfif isdefined("URL.SearchEventType") AND len(URL.SearchEventType)>
<cfset EventType = "AND Event_type LIKE '%#URL.SearchEventType#%'">
<cfelse>
<cfset EventType = "">
</cfif>
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Time Sheet Help 6 54
.dwt files not viewable in browser - why? 2 88
Domino Website - Redirection 12 74
Soundcloud.com 4 21
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now