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

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]