Skip to main content
A
ACS LLC
Inspiring
September 27, 2012
Answered

Query over a form list

  • September 27, 2012
  • 1 reply
  • 1389 views

Can somebody tell me the correct format for querying the database when I need to pick all records that match from a form entry.

Example if somebody enters US,AU,ZA,SG into a form under the form field name form_country

The way I have it does not work

WHERE countryshort = '#form_country#'

This gives:

SELECT DISTINCT(countryLong) AS CountryLong,CountryShort FROM ipcountry2 WHERE countryshort = 'US,AU,ZA,SG' ORDER BY countryLong

I need to have it treat the user entry as a list

Thanks

Mark

This topic has been closed for replies.
Correct answer ACS LLC

You are missing an important attribute in your cfqueryparam tag.


Hi Dan

Ahhh. got it.. I was missing list=yes

It works!

Thanks for pointing me in the right direction

Mark

1 reply

D
Dan_Bracuk
Inspiring
October 7, 2012

In your query, replace the equal sign with the word "in".  Surround your list with parentheses.  Use cfqueryparam with the appropriate attributes.

A
ACS LLCAuthor
Inspiring
October 7, 2012

Tried it but it didn't seem to work, no results when I should have seen some records returned. If I enter one country code US for example it finds all the US records, but if I enter two countries, CA,US it does not find both CA and US records, just returns nothing, as below:

Here's the test script

FORM1.CFM

<html><head></head><body>

<form action="form2.cfm" method="post">

<input type="text" name="form_country">

</form></body></html>

FORM2.CFM

<CFQUERY NAME="searchcountry" DATASOURCE="#datasource#">

    SELECT countrylong

    FROM ipcountry2

    where countryshort IN (<cfqueryparam value="#form_country#" cfsqltype="cf_sql_varchar">)

</CFQUERY>

<CFDUMP VAR="#searchcountry#">

RESULT

query
RESULTSET
query
COUNTRYLONGCOUNTRYSHORTIPFROMIPTO
CACHED false
EXECUTIONTIME 0
SQL SELECT * FROM ipcountry2 where countryshort IN (?)
SQLPARAMETERS
array
1    us,ca   
D
Dan_Bracuk
Inspiring
October 8, 2012

You are missing an important attribute in your cfqueryparam tag.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices