Re: Handling data with that pesky ampersand in a WHERE clause

You shouldn't use a textual description to do what you are trying to 
achieve if possible. Invariably, at some point (such as now) the text 
will give you a ton of headaches because it will contain characters that 
shouldn't go into a URL, especially the ampersand because that is used 
as a delimiter to separate multiple variables you are passing from one 
page to another through the URL (ie: 
http://somesite.com?var1=value&var2=value&var3=value)

Your gallery_category page query is returning 0 results because the 
RecordID variable in the URL is truncated from "T & C Bar" to just "T " 
(or "T%20" as the %20 represents a space) because the #URL.RecordID# 
grabs everything in the URL after "RecordID=" and before the next 
ampersand, which of course is the delimiter.

What I would recommend instead is to give each of your categories a 
unique ID with no scharacters at all (an autoincrementing integer field 
usually works fine for these purposes) then use THAT as the variable to 
pass instead of the actual category text description. Cheers.

Armando

Bonnett, John wrote:
> I don't understand why, but perhaps your need to put a '\' before the &
> before including it in the query. I know you would need to do that if
> the category contained a single quote like "O'Hara". There are a few
> other characters that need to be escaped in sting literals for MySQL but
> I didn't think & was one of them.
> 
> John Bonnett
> 
> -----Original Message-----
> From: Dave Long [mailto:[email protected]] 
> Sent: Wednesday, 18 April 2007 11:05 AM
> To: [email protected]
> Subject: Handling data with that pesky ampersand in a WHERE clause
> 
> Wanting to sort a collection of images by category in a ColdFusion page,
> I set the ID_Field to the category field as shown below:
> 
> 	<CFQUERY name="GetRecord" dataSource="xxxxxx">
> 		SELECT DISTINCT Category, Category AS ID_Field
> 		FROM gallery
> 		WHERE gallery.Active = 1
> 		ORDER BY gallery.Category
> 	</CFQUERY>
> 
> and passed the appropriate RecordID on to the next page:
> 
> 	<a
> href="gallery_category.cfm?RecordID=#ID_Field#">#Category#</a>
> 
> On the gallery_category page, I filter the records with this query:
> 
> 	<CFQUERY name="GetRecord" dataSource="xxxxxx">
> 		SELECT gallery.GalleryID AS ViewField1,
> gallery.ImageFile AS ViewField2, gallery.Caption AS ViewField3,
> gallery.Rank AS ViewField4, gallery.Active AS ViewField5,
> gallery.Category AS ViewField6, gallery.Title AS ViewField7,
> gallery.GalleryID AS ID_Field
> 		FROM gallery
> 		WHERE gallery.Category = '#URL.RecordID#' AND
> gallery.Active = 1
> 		ORDER BY gallery.Category, gallery.Rank
> 	</CFQUERY>
> 
> All is fine UNTIL... until the data entered in a record's category field
> includes an ampersand "&". At that point the query returns 0 records.
> 
> Example: one of the categories is named "T & C Bar". That is the name of
> the bar, not "T and C Bar".
> 
> Any suggestions as to how can I get around this problem?
> 
> Dave Long
> Web Design, Programming, & Hosting
> http://www.northgoods.com
> 
> 
> 
> --
> This message has been scanned for viruses and dangerous content by
> MailScanner, and is believed to be clean.
> 
> 
> 

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]