RE: Help with single quotes

[Cyrill Vatomsky]

Thanks, David

 
  cfquery name="lis_by_city" datasource="xyz"
   SELECT ID, sale_price,Address, City
   FROM properties
   WHERE City IN ('#PreserveSingleQuotes(form.by_city)#')
  /cfquery
 
  If only one city is selected in the form, the processing
  template works fine, displaying all properties for a selected
  city. But if two or three are selected, then nothing is displayed,
  i.e. query does not retrieve any records.

 The PreserveSingleQuotes function won't automatically add quotes
 for you, it
 just keeps the ones that you might already have. If I understand your
 problem correctly, what you really need to do is actually wrap
 single quotes
 around each city value returned by the form. For example, if the
 user picked
 the cities New York, Baltimore, and Orlando, you'd want your query to look
 like this:

 ... WHERE City IN ('New York','Baltimore','Orlando')

 However, if you simply output the variable Form.by_city, it would probably
 look like this:

 New York,Baltimore,Orlando

Exactly. I went to the form itself and changed the input value to
"#'city'#". Works now


 The function you want to use instead is the ListQualify function, which as
 used below will wrap each item with single quotes:

 ... WHERE City IN (#ListQualify(Form.by_city, "'")#)

 An even better answer, but one which would require a bit more work at this
 point, would be to store cities in their own table, then reference them by
 ID when needed. Normalizing your data is very important.

It might be somewhat off-topic, but I as a relative newbie was scared off by
some books warning against overnormalizing. In this case, since the database
will not be over 2000-3000 records, I chose against normalizing.

Mistake?

Cyrill Vatomsky
e-newworldorder.com

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: Help with single quotes

[Dave Watts]

 I have a list of cities in a form with multiple enabled.
 Selected list is submitted (as form.by_city) to a template
 with the following query:

 cfquery name="lis_by_city" datasource="xyz"
  SELECT ID, sale_price,Address, City
  FROM properties
  WHERE City IN ('#PreserveSingleQuotes(form.by_city)#')
 /cfquery

 If only one city is selected in the form, the processing
 template works fine, displaying all properties for a selected
 city.

 But if two or three are selected, then nothing is displayed,
 i.e. query does not retrieve any records.

 If I remove single quotes around ##, I get "Invalid Number of
 Parameters, Expected 1"

 I have similar queries that work OK with IDs in the same way:

 WHERE ID IN (#PreserveSingleQuotes(form.by_type)#)

 //by_type is a list genetrated by a multiple select where values
 are integers. In the City example above, option values are,
 naturally, strings.

The PreserveSingleQuotes function won't automatically add quotes for you, it
just keeps the ones that you might already have. If I understand your
problem correctly, what you really need to do is actually wrap single quotes
around each city value returned by the form. For example, if the user picked
the cities New York, Baltimore, and Orlando, you'd want your query to look
like this:

... WHERE City IN ('New York','Baltimore','Orlando')

However, if you simply output the variable Form.by_city, it would probably
look like this:

New York,Baltimore,Orlando

The function you want to use instead is the ListQualify function, which as
used below will wrap each item with single quotes:

... WHERE City IN (#ListQualify(Form.by_city, "'")#)

An even better answer, but one which would require a bit more work at this
point, would be to store cities in their own table, then reference them by
ID when needed. Normalizing your data is very important.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.