Solved

Records are being skipped in search results

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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 …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

18 Experts available now in Live!

Get 1:1 Help Now