Solved

Records are being skipped in search results

Posted on 2003-11-22
2
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

636 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