Invoke promt for Where Clause

Report · Feb 25, 2021

Is there any way to have the page prompt for data and use that in a where clause? ponumber =

????

Report · Feb 25, 2021

John, the answer is yes. But first can you confirm that you mean to be asking this in the forums for ColdFusion? (Sometimes people ask questions meant for other Adobe forums, so it's worth confirming for your sake.)

If you do, then can you confirm that you are asking about making a web form that would ask that?

Finally, is it perhaps that you are not a cfml developer (the language of cf)? Do you come from any other web app server platform, like php? Are you familiar with html already (the language of web forms)?

I ask because what you seek is such a very fundamental thing to do with cf (offer and process web page forms and pass the sql to a db) that while the simple answer to your question is yes, it would seem there's maybe more to the question. Or there's certainly more that could be offered as an answer, depending on where you're coming from.

Simply, if you have a form field called age, you could use that form field in a where like this :

Where age=#form.age#

But then for security it would be recommended to use:

Where age=<cfqueryparam value="#form.age#" cfsqltype="cf_sql_integer" >

Even then, there is more to consider if what you are passing is a string or list, and so on.

As for the form, it could have a simple text field like:

Or there are select fields and more, and then these would be in an html form tag with an action pointing to a cfml page and a method="post". But you can see how the question is broad enough to have many ways to answer it. 🙂

So share a bit more, or let us know if this was enough.

/Charlie (troubleshooter, carehart.org)

Report · Feb 25, 2021

I know that I can do it with a form. I wrote a template which inserts certain reports using urlID. I am wondering if I can have a box pop up asking for the POnumber and have the page reload and perform the query. I am thinking javascript, but am wondering if there is a way to do it without a form posting to a page that processes it.

Report · Feb 25, 2021

Yes, via Javascript.

/Charlie (troubleshooter, carehart.org)

Report · Feb 28, 2021

@johng58900177 : Is there any way to have the page prompt for data and use that in a where clause? ponumber =

Yes. Here is an example:

<body onload="userInputFuction()">
	<cfif isdefined("form.ponumber")>
		<!--- Sample code --->
		<!---
		<cfquery datasource="testdb" name="q">
			SELECT * 
			FROM users
            WHERE ponumber = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.ponumber#">
		</cfquery>
		--->
		
		<!--- Test code --->
		<cfdump var="#form#" label="Submitted form">
	<cfelse>
		<script type="text/javascript">	
			function userInputFuction(ponumber) {
				var ponumber = prompt ("Please enter your ponumber",""); 
			    document.getElementById("poNumberInput").value = ponumber;
			
				document.getElementById("userInputForm").submit();
			}
		</script>
		<cfoutput><form id="userInputForm" method="post" action="#CGI.SCRIPT_NAME#"></cfoutput>
			<input name="ponumber" id="poNumberInput" type="text" ><br>
		</form>
	</cfif>
</body>

Adobe Community

Invoke promt for Where Clause