Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Records are being skipped in search results

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
McHack
Asked:
McHack
  • 2
1 Solution
 
jyokumCommented:
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
 
jyokumCommented:
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

Hire Technology Freelancers with Gigs

Work with 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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now