Multiple Search Parameters in one Form

**acoder** · Aug 1 '08, 05:35 PM

If it's in that format, you can use it as a list, or use the IN keyword in your SQL statement.

**reddem0n** · Aug 1 '08, 06:09 PM

Sorry I'm a little ignorant when it comes to this...What exactly do you mean by that? Right now here is my coding on the search page where the user would input the search query:

Code:

<HTML>
<HEAD>
	<TITLE>Bad Emails / Phone Compiler v0.1</TITLE>
	
<script language="JavaScript" >
  
  
function checkText(Item, Value)
{
 var next=Item.tabIndex;
 for(i=1; i <  document.getElementById("searchForm101").length; i++)
 {
  if(i == next)
  {
   // leave textfield as is.
  }
  else
  {
   document.getElementById("searchForm101").elements[i].value  = ""; // Erase other fields
  }
 }
 document.getElementById("searchForm101").elements[document.getElementById("searchForm101").length - 1].value  = "Search"; // Update the entry 
}

</script>

<script language="JavaScript" > 
function validate()
{
 var count=0;
 for(i=1; i <  document.getElementById("searchForm101").length; i++)
 {
  if (document.getElementById("searchForm101").elements[i].value  == "")
  {
   count++;
  }
 }
 if(count == (document.getElementById("searchForm101").length - 2) )
 {
  var newHTML = "<span style='color:#ff0000'>" + 'Please enter text.' + "</span>";
  document.getElementById('ErrorMessage').innerHTML = newHTML;
  return false;
 }
 else
 {
  return true;
 }
}	
</script>

</HEAD>

<BODY bgcolor="ffffff">
<CENTER>
<TABLE cellpadding="0" cellspacing="0" border="0" width="300" bgcolor="dddddd">
	<TR bgcolor="008800">
		<TD>
			<TABLE cellpadding="0" cellspacing="0" border="0" width="100%">
				<TR>
					<TD align="left"><a href="<cfoutput>#blahblah_blahblah_com#</cfoutput>/"><font color="ffffff"><b>Main Menu</b></font></a></TD>
					<TD align="right"><font color="ffffff"><b>Bad Emails / Phone Compiler v0.1</b></font></TD>
				</TR>
			</TABLE>
		</TD>
	</TR>
	<tr>
	<div id="ErrorMessage" >&nbsp
	</div>
	</tr>
<FORM method="post" name="searchform" action="getresults.cfm"  id="searchForm101">
<INPUT type="hidden" name="searchflag" value="2" />
	<TR>
		<TD><b>Email:</b><BR>
		  <textarea name="email" cols="80" rows="30" tabindex="1" onChange="checkText(this, this.value)" onClick="checkText(this, this.value)"></textarea></TD>
	</TR>
		<TD align="center"><Input type="submit" name="submitbutton"  value="Search" onClick="return validate()" /></TD>
    </TR>
</FORM>
<!---
<FORM method="post" action="getresults.cfm">
<INPUT type="hidden" name="searchflag" value="1">
	<TR>
		<TD align="center"><br><br><hr><br><INpUT type="submit" name="SHOWALL" value="Show ALL"></TD>
	<TR>
</FORM>
--->
</TABLE>
</CENTER>

Once the user submits the query, the output results gets shown on the getresults.cfm page with the email the user entered and the system basically matches the email it finds on the database with the phone # and puts it in the table.

Code:


<cfparam name="searchflag" default="-1">
<!---
<cfparam name="email" default="">
<cfparam name="lastname" default="">
<cfparam name="IDNUM" default="0">
--->

<CFIF IsDefined("Form.IDNUM") and (Form.IDNUM NEQ "")>
 <cfoutput>
	<CFQUERY name="GetInfo" datasource="#ODBC_Datasource#">
		Select Gaming_clients.clientid, Gaming_clients.lastname, Gaming_clients.firstname, Gaming_clients.email, Gaming_clients.phone
		from Gaming_clients WITH (NOLOCK)
		WHERE Gaming_clients.idnum = #form.idnum#
		ORDER by Gaming_clients.lastname
	</CFQUERY>
</cfoutput>	
<cfelseif IsDefined("Form.lastname") and (Form.lastname NEQ "")>
<cfoutput> 
	<CFQUERY name="GetInfo" datasource="#ODBC_Datasource#">
		Select Gaming_clients.clientid, Gaming_clients.lastname, Gaming_clients.firstname, Gaming_clients.email, Gaming_clients.phone
		from Gaming_clients WITH (NOLOCK)
		WHERE Gaming_clients.lastname LIKE '%#trim(lastname)#%'
		ORDER by Gaming_clients.lastname
	</CFQUERY>
</cfoutput>
<cfelseif IsDefined("Form.email")and (Form.email NEQ "")>
<cfoutput>
	<CFQUERY name="GetInfo" datasource="#ODBC_Datasource#">
    <cfloop index="i" list="#form.searchflag#" delimiters=" ">
		Select Gaming_clients.clientid, Gaming_clients.lastname, Gaming_clients.firstname, Gaming_clients.email, Gaming_clients.phone
		from Gaming_clients WITH (NOLOCK)
		WHERE Gaming_clients.email LIKE '%#trim(email)#%'
		ORDER by Gaming_clients.lastname
        </cfloop>
	</CFQUERY>
</cfoutput>
</cfif>


<!---  This redirects to page that shows all resumes
<CFIF ParameterExists(SHOWALL)>
	<CFLOCATION url="viewresumes.cfm">
  <CFABORT>
</CFIF>
--->

<HTML>
<HEAD>
	<TITLE>Bad Emails / Phone Compiler v0.1 Results</TITLE>
</HEAD>
<FORM method="post" action="viewresumes.cfm">
<INPUT type="hidden" name="clientid_required" value="Please choose someone to edit.">
<CFOUTPUT>
<INPUT type="hidden" name="searchflag" value="#searchflag#">
</CFOUTPUT>
<BODY bgcolor="ffffff">
<CENTER>
<TABLE bgcolor="dddddd" cellpadding="0" cellspacing="0" border="0" width="500">
	<TR bgcolor="008800">
		<TD colspan="3">
			<TABLE cellpadding="0" cellspacing="0" border="0" width="100%">
				<TR>
					<TD align="left"><a href="<cfoutput>#blahblah_blahblah_com#</cfoutput>/"><font color="ffffff"><b>Main Menu</b></font></a></TD>
					<TD align="right"><font color="ffffff"><b>Bad Emails v0.1</b></font></TD>
				</TR>
				<tr><td colspan="2" >
<!---

<CFIF IsDefined("Form.IDNUM") and (Form.IDNUM NEQ "")>
<cfoutput>	<h1>IDNUM is - #Form.IDNUM#</h1></cfoutput>	
<cfelseif IsDefined("Form.lastname") and (Form.lastname NEQ "")>
<cfoutput> 	<h1>Last Name is - #Form.lastname#</h1></cfoutput>
<cfelseif IsDefined("Form.email")and (Form.email NEQ "")>
<cfoutput> 	<h1>email is - #Form.email#</h1></cfoutput>
</cfif>
--->			
				</td></tr>
			</TABLE>
		</TD>
	</TR>

<cfif isDefined("GetInfo.Recordcount")>
<CFIF #GetInfo.Recordcount# IS 0>
	<TR>
		<TD colspan="3" align="center">No matches were found</TD>
	</TR>
	<TR>
		<TD colspan="3" align="center"><a href="search.cfm">New Search</a></TD>
	</TR>
<CFELSE>
  <TR>
		<TD><b>Email</b></TD>
        <td><b>Phone</b></td>
	</TR>
<CFOUTPUT query="GetInfo">
	<TR>
		<TD>#email#</TD>
        <td>#phone#</td>
	</TR>
</CFOUTPUT>

	<TR>
		<TD colspan="3">
			<TABLE cellpadding="0" cellspacing="0" border="0" width="100%">
				<TR>
					</FORM>
					<FORM method="post" action="search.cfm">
					<TD align="center"><INPUT type="submit" value="Go Back"></TD>
					</FORM>
				</TR>
			</TABLE>
		</TD>
	</TR>

</CFIF>
</cfif>

	<!---
<cfelse>
	<TR>
		<TD colspan="3">You may have pushed the "enter" key on the previous page - please <a href="javascript:history.go(-1);">go back</a> and push one of the 2 search buttons.</TD>
	</TR>
</cfif>
--->
 </TABLE>
</CENTER>
</BODY>
</HTML>

**acoder** · Aug 1 '08, 07:34 PM

On line 32, you could replace "LIKE '%#trim(email)# %'" with "IN ('#trim(email)# ')".

Note that this assumes you've validated the input and you should use cfparam/cfqueryparam to prevent sql injection attacks.

**reddem0n** · Aug 1 '08, 08:09 PM

thanks for your response

Code:

<cfoutput>
	<CFQUERY name="GetInfo" datasource="#ODBC_Datasource#">
    <cfloop index="i" list="#form.searchflag#" delimiters=",">
		Select Gaming_clients.clientid, Gaming_clients.lastname, Gaming_clients.firstname, Gaming_clients.email, Gaming_clients.phone
		from Gaming_clients WITH (NOLOCK)
		WHERE Gaming_clients.email IN ('#trim(email)#')'
		ORDER by Gaming_clients.lastname
        </cfloop>
	</CFQUERY>
</cfoutput>
</cfif>

Here is what i have now...this didn't do anything tho...whenever i enter multiple email addresses in the form, it results to no match found

**acoder** · Aug 1 '08, 09:55 PM

Sorry, forgot the single quotes around each email. You'll need to do to wrap quotes around each email string. You could treat the string as a list with the comma as delimiter and use cfloop to loop over and add quotes and concatenate again.

Multiple Search Parameters in one Form

Multiple Search Parameters in one Form

Comment

Comment

Comment

Comment

Comment