Solved

Records are being skipped in search results

Posted on 2003-11-22
2
189 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 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