Query over a form list

Report · Sep 27, 2012

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

Report · Oct 07, 2012

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

Report · Oct 07, 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

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

FORM2.CFM

SELECT countrylong

FROM ipcountry2

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

</CFQUERY>

RESULT

query

RESULTSET

query
	COUNTRYLONG	COUNTRYSHORT	IPFROM	IPTO

CACHED

false

EXECUTIONTIME

0

SQL

SELECT * FROM ipcountry2 where countryshort IN (?)

SQLPARAMETERS

array
1	us,ca

Report · Oct 08, 2012

You are missing an important attribute in your cfqueryparam tag.

Report · Oct 08, 2012

Hi Dan

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

It works!

Thanks for pointing me in the right direction

Mark

Query over a form list

1 Correct answer