We help IT Professionals succeed at work.

How can make a open Query?

jriver12
jriver12 asked
on
When I say open I mean in the sence that is have a form with lets say 2 fields.

name = my name
company= a company

on submit I would like to search for one of these fields(only if the field is populated)

I have been using the
<cfif Isdefined(#name#)>
<myquery1>

<cfoutput name=myquery1>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>

<cfif isdefined("company">
<myquery2>

<cfoutput name=myquery2>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>
 
but have not been getting positive results.

I know that it must be a syntax issue.

HELP :(




Comment
Watch Question

Commented:

The basics you need is:
<cfif Isdefined("FORM.name") AND TRIM(FORM.NAME) NEQ "">
<myquery1>
<cfif myquery1.recordcount GT 0>
<cfoutput name=myquery1>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>
<cfelse>
  No Name entered.
</cfif>

<cfif isdefined("FORM.company") AND TRIM(FORM.COMPANY) NEQ "">
<myquery2>
<cfif myquery2.recordcount GT 0>
<cfoutput name=myquery2>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>
<cfelse>
  No company entered.
</cfif>

Author

Commented:
workd great. one more thing CJ how can I ensure that both fields would not be populated at the same time, I want a one or the other type of situation>

Commented:
jriver12,

Adding ... either one type situation, you can use OR instead of AND in your <cfif> tag.

sshhz

Commented:
You can add another condition at the top:
<cfif Isdefined("FORM.name") AND TRIM(FORM.NAME) NEQ ""
AND isdefined("FORM.company") AND TRIM(FORM.COMPANY) NEQ "">
Please pick one or the other...
<cfelse>
<cfif Isdefined("FORM.name") AND TRIM(FORM.NAME) NEQ "">
<myquery1>
<cfif myquery1.recordcount GT 0>
<cfoutput name=myquery1>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>
<cfelse>
 No Name entered.
</cfif>

<cfif isdefined("FORM.company") AND TRIM(FORM.COMPANY) NEQ "">
<myquery2>
<cfif myquery2.recordcount GT 0>
<cfoutput name=myquery2>show the returns of the query</CFoutput>
<cfelse>
no records found
</CFIF>
<cfelse>
 No company entered.
</cfif>
</cfif>

Author

Commented:
sshhz
Can you explain,

I have changed the and to an or on this line

cfif Isdefined("FORM.name") AND TRIM(FORM.NAME) NEQ "">

and have received  the same results.

I just want to be able to search on what ever field is filled in and not allow for both to be searched in simotaneously. It should only be one or the other.

Commented:
did you try my latest post?

Author

Commented:
cj,
No. i just refreshed and got it..

wonder when the email notifications will be back up.

Author

Commented:
cj, we all now how users will try to submit a blank form, so how do you verify that at least one field is filled in with out doing jscript validation. and if they do do that then just let them now that they must have one field filled out.:

I have posted the code that you have supplied me with and I have added some comments.  I think that I am now understanding what the code is doing.  let me know if I am right in the understanding.

<html>
<head>
     

<title>Untitled</title>
</head>

<body>
<!--- Defining what the one or the other statu --->
<cfif Isdefined("FORM.firstname1") AND TRIM(FORM.firstNAME1) NEQ ""
AND isdefined("FORM.lastname") AND TRIM(FORM.lastname) NEQ "">
<!--- If all fields are filled out then tell the user --->
Please pick one or the other...
<cfelse>
<!--- check to see if the firstname field is defined --->
<cfif Isdefined("FORM.firstname1") AND TRIM(FORM.firstname1) NEQ "">
<!---  if firstname field is defined, run the query. --->
<CFQUERY NAME="FNAME" DATASOURCE="test">
SELECT      FirstName1, LastName, Address, City, StateOrProvince, PostalCode
FROM         Addresses
WHERE       (FirstName1 = '#form.firstname1#')
</CFQUERY>
<cfif fname.recordcount GT 0>
<!--- Display results if first name is defined --->
show the returns of the query on first name.
<cfoutput query="fname">
#FirstName1#, #LastName#<br>
#Address#<br>
#City#, #StateOrProvince# #PostalCode#</CFoutput>
<cfelse>
Your search on <cfoutput>#fname.firstname1#</cfoutput> Returned no results.
</CFIF>
</cfif>

<!--- check to see if last name is defined --->
<cfif isdefined("FORM.lastname") AND TRIM(FORM.lastname) NEQ "">
<!--- if so run the query --->
<CFQUERY NAME="LNAME" DATASOURCE="test">
SELECT      AddressesID, FirstName1, LastName, Address, City, StateOrProvince, PostalCode
FROM         Addresses
WHERE       (LastName = '#FORM.LASTNAME#')
</CFQUERY>
<!--- If records are found or results are greater than o, display results. --->
<cfif Lname.recordcount GT 0>
show the returns of the query on last name
<cfoutput query="lname">
#FirstName1#, #LastName#<br>
#Address#<br>
#City#, #StateOrProvince# #PostalCode#
</CFoutput>
<cfelse>
no records found
</CFIF>
</cfif>
</cfif>
</body>
</html>

Commented:
Yes you are following the code right.

as far as javascript validation here:

<!--- Function to remove whitespace for JS 1.0 --->
<SCRIPT LANGUAGE="JavaScript1.1" TYPE="text/javascript">
function trimString (str) {
  while (str.charAt(0) == ' ')
    str = str.substring(1);
  while (str.charAt(str.length - 1) == ' ')
    str = str.substring(0, str.length - 1);
  return str;
}
</SCRIPT>
<!--- Function to remove whitespace for JS 1.2 --->
<SCRIPT LANGUAGE="JavaScript1.2" TYPE="text/javascript">
function trimString (str) {
  str = this != window? this : str;
  return str.replace(/^\s+/g, '').replace(/\s+$/g, '');
}
</SCRIPT>
<!--- Validate function --->
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
     function validateForm(frm) {
       var firstnameStr = trimString(frm.firstname.value);
       var lastnameStr = trimString(frm.lastname.value);    
       if ((firstnameStr == "") && (lastnameStr == "")) {
            alert("Please enter either a First or a Last name");
          return false;
       }
       else {
         frm.firstname.value = firstnameStr;
          frm.lastname.value = lastnameStr;
            return true;
       }
     }
</SCRIPT>

<FORM ACTION="action.cfm" OnSubmit="return validateForm(document.searchform);" NAME="searchform">
First Name: <INPUT TYPE="text" NAME="firstname"><br>
Last Name: <INPUT TYPE="text" NAME="lastname"><br>
<INPUT TYPE="submit" NAME="Submit" VALUE="Search">
</FORM>

Author

Commented:
cheekycj

I knew that it could be done with Javascript but is there a way to do the validation using the same (above mentioned) CF script?

Commented:
jriver12,

Been reading the threads ... wow, lots of codes. Just try to really understand what you want over here. If you are looking for either one field is entered and it will run the search based on the entered field .. if this is true, could you plug this code in and try to run the testing, see whether it works well for you. Currently the code below was checking either firstname1 or lastname is entered, if either one of them is entered, then run the search ... if both are entered, prompt the msg "pick another one pls" and abort it.

Hope this really what you are looking for. Good Luck.

sshhz

<!--- if both fields are entered, prompt msg and abort the search. --->
<!--- rules: either one field is field ! --->
<cfif trim(form.firstname1) NEQ "" AND TRIM(FORM.lastname) NEQ "">
     pick another one pls ...
     <cfabort>
</cfif>

<!--- if firstname field is entered --->
<cfif trim(form.firstname1) NEQ "">
     <!---  if firstname field is defined, run the query. --->
     <CFQUERY NAME="FNAME" DATASOURCE="test">
     SELECT      FirstName1, LastName, Address, City, StateOrProvince, PostalCode
     FROM         Addresses
     WHERE       (FirstName1 = '#form.firstname1#')
     </CFQUERY>
     <cfif fname.recordcount GT 0>
          <!--- Display results if first name is defined --->
          show the returns of the query on first name.
          <cfoutput query="fname">
          #FirstName1#, #LastName#<br>
          #Address#<br>
          #City#, #StateOrProvince# #PostalCode#</CFoutput>
     <cfelse>
          Your search on <cfoutput>#fname.firstname1#</cfoutput> Returned no results.
     </CFIF>
</cfif>

<!--- if lastname field is entered --->
<cfif TRIM(FORM.lastname) NEQ "">
     <!--- if so run the query --->
     <CFQUERY NAME="LNAME" DATASOURCE="test">
     SELECT      AddressesID, FirstName1, LastName, Address, City, StateOrProvince, PostalCode
     FROM         Addresses
     WHERE       (LastName = '#FORM.LASTNAME#')
     </CFQUERY>
     <!--- If records are found or results are greater than o, display results. --->
     <cfif Lname.recordcount GT 0>
          show the returns of the query on last name
          <cfoutput query="lname">
          #FirstName1#, #LastName#<br>
          #Address#<br>
          #City#, #StateOrProvince# #PostalCode#
          </CFoutput>
     <cfelse>
          no records found
     </CFIF>
</cfif>

Commented:
CFScript is server side so my previous comment with the CF check will do the same thing as CFScript will.

With a Combo of the JavaScript check and CF code check. You can't go wrong.

CJ

Author

Commented:
thanks cj,

I am begining to believe that you live on this site. ;)

Commented:
I like to help, plus it is kinda addicting (helping on this site that is)

CJ

Author

Commented:
I do appreciate it.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.