Link to home
Start Free TrialLog in
Avatar of iolike
iolike

asked on

Problem building a Search Interface that can Search with more than one parameter

I built an application where users can use Search form to search for data, and have the result display in a dynamic table.

In the search form I have dropdown lists and I want the user to pick from any of the list and have the result display in a table on another page. for some reason the for does not seem to work right,and being new to Coldfusion I am at lost on what to do Please Help. My search form looks like this:

<cfparam name="form.ConNumber" default="%">
<cfparam name="form.ItemNo" default="">
<cfparam name="form.Name" default="">
<cfparam name="form.Date" default="">
<cfquery name="spSearchForm" datasource="SpeakerCard">
SELECT *
FROM SpeakerCard
WHERE 0=0
<cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
<cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
<cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
<cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Speaker Card Search Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style1 {
      font-family: Arial, Helvetica, sans-serif;
      font-size: 14px;
      font-weight: bold;
}
.style2 {font-size: 16px}
-->
</style>
</head>

<body bgcolor="#CCFFFF">
<p>&nbsp;</p>
<p>&nbsp;</p>
<p align="center" class="style1 style2"><strong>Search Form</strong></p>
<table width="25%" align="center" >
  <tr>
    <td><div align="center" class="style14 style17"><strong><cfoutput>#DateFormat(Now(), "mmmm dd, yyyy")#</cfoutput> </strong></div></td>
    <td><cfoutput><span class="style14 style1 style17"><strong>#TimeFormat(Now(), "hh:mm:sstt")#</strong></span></cfoutput></td>
  </tr>
</table>
<div align="right">
  <input type="submit" name="Submit2" value="Back to Menu Page" style="font:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bolder" onClick="window.open('SearchandReportMenu.cfm');" onMouseOver="this.style.color='red';" onMouseOut="this.style.color='black';" >
</div>
<hr size="5" color="#333333">
<form action="Report.cfm" method="post" name="SearchForm" id="SearchForm">
  <p align="center" class="style1">Con<br>
    Number:<br>
    <input name="ConNumber" type="text" id="ConNumber" size="20" maxlength="25">    
    <br>
    <br>
    Item Number/<br>
    <input name="ItemNo" type="text" id="ItemNo" size="30" maxlength="35">
  </p>
  <p align="center" class="style1">Name:<br>
    <select name="Name" size="1" id="Name">
      <option value=""></option>
      <cfoutput query="SearchForm">
      <option value="#SearchForm.Name#">#SearchForm.Name#</option>
    </cfoutput>    
    </select>
  </p>
<p align="center" class="style1">Date:<br>
    <select name="Date" size="1" id="Date">
      <option value=""></option>
      <cfoutput query="SearchForm">
      <option value="#SearchForm.Date#">#SearchForm.Date#</option>
    </cfoutput>    
    </select>
  </p>
    <p align="center" class="style1"><span class="style1">
    <input type="submit" name="Submit" value="Search">
  </span> </p>
</form>
<p>&nbsp;</p>
</body>
</html>

Please Help Thank You
Avatar of Jerry_Pang
Jerry_Pang
hello there,

remove the
<cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
<cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
<cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
<cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>

outside of the cfquery

then do something like

<cfset SQLparameters = "">
<cfif form.ConNumber NEQ "">
 <cfset SQLparameters = SQLparameters  & "AND ConNumber LIKE '%#form.ConNumber#%'"></cfif>
<cfif form.ItemNo NEQ "">
 <cfset SQLparameters = SQLparameters  & "AND ItemNo = '#form.ItemNo#'"</cfif>
<cfif form.Name NEQ ""> 
 <cfset SQLparameters = SQLparameters  & "AND Name= '#form.Name#'"</cfif>
<cfif form.Date NEQ ""> 
 <cfset SQLparameters = SQLparameters  & "AND Date = '#form.Date#'"</cfif>

then  on your query

<cfquery name="spSearchForm" datasource="SpeakerCard">
SELECT *
FROM SpeakerCard
WHERE 0=0
#SQLparameters#
</cfquery>
Avatar of Jerry_Pang
Jerry_Pang
syntax errors on my code above found near the </cfif>
correct it by adding a >.

regards.
Avatar of anandkp
anandkp
Flag of India image
if ur looking for something like a Google search or EE search above - where u can search thru DB with multiple words or combinations of them - by using " & + ...
BooleanSearch is the way to go abt using it - i had got this file from the net & have been using it since quite some yrs now :)
but i dunno if u can find the link online - thou u cldfind the syntax for it on this link : https://www.experts-exchange.com/questions/21006055/Searching-a-database-using-quoted-strings.html
copy the entire file code from there & save it as booleansearch.cfm & then use it as a custom tag & it does the job for u - simple :)


K'Rgds
Anand
Avatar of iolike
iolike

ASKER

Thank you for your help.  I modified the query and it works fine but the result page does not seem to output the right results.

I want the users to search with ItemNo and any other data and have the result page show the search criteria. e.g if I search with ItemNo and Date, I want the reult page to show the itemno, date and any other data I want.

I have the following query for the result page:

<cfparam name="FORM.AgendaItemNo" default="">
<cfparam name="FORM.Comm_CouncilDate" default="">
<cfparam name="FORM.Comm_CouncilName" default="">
<cfquery name="spResults" datasource="SpeakerCard">
SELECT *
FROM SpeakerCard
WHERE AgendaItemNo IN ('#FORM.AgendaItemNo#', '#FORM.Comm_CouncilDate#', '#FORM.Comm_CouncilName#')
ORDER BY SpeakerCard.AgendaItemNo, Comm_CouncilDate, Comm_CouncilName</cfquery>

Then I have my output table.

How do I fix this
Avatar of campbelc
campbelc
Are you simply looking to populate you search form with some variables from your database, then search based on those variables that you've entered or selected in your form, then display the search results while still giving you user the ability to perform another search?

If so, will have a solution in about 2 minutes for you.
ASKER CERTIFIED SOLUTION
Avatar of campbelc
campbelc
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of campbelc
campbelc
Sorry, keep adding more and more features on to this: =)


<cfquery name="SearchFormNames" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by name
</cfquery>
<cfquery name="SearchFormDates" datasource="ChrisForTest">
  SELECT * FROM SpeakerCard order by date
</cfquery>

<title>Speaker Card Search Form</title>

<style type="text/css">
<!--
  .style1 {font-family: Arial, Helvetica, sans-serif;font-size: 14px;font-weight: bold;}
  .style2 {font-size: 16px}
  .style3 {font-family: Arial, Helvetica, sans-serif;font-size: 11px;font-weight: bold;color:#ffffff;}
-->
</style>
</head>

<body bgcolor="#CCFFFF">
<p align="center" class="style1 style2"><strong>Search Form</strong></p>
<table width="25%" align="center" >
  <tr>
    <td><div align="center" class="style14 style17"><strong><cfoutput>#DateFormat(Now(), "mmmm dd, yyyy")#</cfoutput> </strong></div></td>
    <td><cfoutput><span class="style14 style1 style17"><strong>#TimeFormat(Now(), "hh:mm:sstt")#</strong></span></cfoutput></td>
  </tr>
</table>
<div align="right">
  <input type="button" name="Submit2" value="Back to Menu Page" style="font:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bolder" onClick="window.open('SearchandReportMenu.cfm');" onMouseOver="this.style.color='red';" onMouseOut="this.style.color='black';" >
</div>
<hr size="5" color="#333333">
<form action="report.cfm?a=results" method="post" name="SearchForm" id="SearchForm">
  <p align="center" class="style1">Con<br>
    Number:<br>
    <input name="ConNumber" type="text" id="ConNumber" size="20" maxlength="25">    
    <br>
    <br>
    Item Number/<br>
    <input name="ItemNo" type="text" id="ItemNo" size="30" maxlength="35">
  </p>
  <p align="center" class="style1">Name:<br>
    <select name="Name" size="1" id="Name">
      <option value=""></option>
      <cfoutput query="SearchFormNames" group="name">
      <option value="#SearchFormNames.Name#">#SearchFormNames.Name#</option>
    </cfoutput>    
    </select>
  </p>
<p align="center" class="style1">Date:<br>
    <select name="Date" size="1" id="Date">
      <option value=""></option>
      <cfoutput query="SearchFormDates" group="date">
      <option value="#SearchFormDates.Date#">#SearchFormDates.Date#</option>
    </cfoutput>    
    </select>
  </p>
    <p align="center" class="style1"><span class="style1">
    <input type="submit" name="Submit" value="Search">
  </span> </p>
</form>
<p>&nbsp;</p>

<cfif isdefined("url.a")>
  <cfquery name="SearchForm2" datasource="ChrisForTest">
    SELECT * FROM SpeakerCard WHERE 0=0
      <cfif form.ConNumber NEQ "">AND ConNumber LIKE '%#form.ConNumber#%'</cfif>
      <cfif form.ItemNo NEQ "">AND ItemNo = '#form.ItemNo#'</cfif>
      <cfif form.Name NEQ ""> AND Name= '#form.Name#'</cfif>
      <cfif form.Date NEQ ""> AND Date = '#form.Date#'</cfif>
  </cfquery>
  <cfif SearchForm2.RecordCount EQ 0>
    <script language="javascript">
        alert('Your search found 0 records');
        self.location='report.cfm';
      </script>  
  </cfif>
  <table align="center" width="700" cellpadding="3" cellspacing="1" bgcolor="cccccc">
    <tr bgcolor="808080">
        <td class="style3">Con Number:</td>
        <td class="style3">Item Number:</td>
        <td class="style3">Name:</td>
        <td class="style3">Date:</td>
    </tr>
    <cfoutput query="searchform2">
    <tr bgcolor="ffffff">
        <td>#connumber#</td>
        <td>#itemno#</td>
        <td>#name#</td>
        <td>#date#</td>
    </tr>
    </cfoutput>
  </table>
</cfif>
<script language="javascript">
  document.SearchForm.ConNumber.focus();
</script>
</body>
</html>
Avatar of campbelc
campbelc
Thank you ;)