List:MySQL on Win32« Previous MessageNext Message »
From:John Bonnett Date:April 18 2007 2:24am
Subject:RE: Handling data with that pesky ampersand in a WHERE clause
View as plain text  
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:dave@stripped] 
Sent: Wednesday, 18 April 2007 11:05 AM
To: win32@stripped
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.


Thread
Handling data with that pesky ampersand in a WHERE clauseDave Long18 Apr
  • RE: Handling data with that pesky ampersand in a WHERE clauseJohn Bonnett18 Apr
    • Re: Handling data with that pesky ampersand in a WHERE clauseArmando18 Apr
      • RE: Handling data with that pesky ampersand in a WHERE clauseDave Long18 Apr
        • Re: Handling data with that pesky ampersand in a WHERE clauseRandy Clamons18 Apr
          • RE: Handling data with that pesky ampersand in a WHERE clauseDave Long18 Apr