jriver12
asked on
How can make a open Query?
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 :(
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 :(
ASKER
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>
jriver12,
Adding ... either one type situation, you can use OR instead of AND in your <cfif> tag.
sshhz
Adding ... either one type situation, you can use OR instead of AND in your <cfif> tag.
sshhz
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>
<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>
ASKER
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.
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.
did you try my latest post?
ASKER
cj,
No. i just refreshed and got it..
wonder when the email notifications will be back up.
No. i just refreshed and got it..
wonder when the email notifications will be back up.
ASKER
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 isdefined("FORM.lastname")
<!--- 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
<!--- 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.firstname
</CFIF>
</cfif>
<!--- check to see if last name is defined --->
<cfif isdefined("FORM.lastname")
<!--- 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>
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 isdefined("FORM.lastname")
<!--- 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
<!--- 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.firstname
</CFIF>
</cfif>
<!--- check to see if last name is defined --->
<cfif isdefined("FORM.lastname")
<!--- 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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.firstname
</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>
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.firstname
</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>
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
With a Combo of the JavaScript check and CF code check. You can't go wrong.
CJ
ASKER
thanks cj,
I am begining to believe that you live on this site. ;)
I am begining to believe that you live on this site. ;)
I like to help, plus it is kinda addicting (helping on this site that is)
CJ
CJ
ASKER
I do appreciate it.
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>