Pound sign in mySQL database converts to question mark in coldfusion display

I am using CartWeaver ColdFusion , and a mySQL database for an ecommerce store.

The product descriptions are entered through a simple text area form.
In the mySQL db, I can see the actual  £ sign in the text field
But when displayed on the page, it converts to ?

see
http://thewholekaboodle.com.wehostwebsites.com/Details.cfm?ProdID=130&category=

I considered replacing all the pound signs in the database with £  , but that doesnt help me, since new products typed in will still be using the pound character ( not wanting to ask client to use £ every time! )

I tried a CF rereplace of the ? that is getting passed to the page, but it expects a regular expression after the ? so that doesnt work either!

What do I need to do, in the database, the entry form, or the display page ( or any of the above ) to get the £ to display as £ in the db output?

The page handles £ just fine in general, to see it, add anything to your cart.
but for some reason in this description field, it is a no go.

MichaelEvangelistaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SheeriConnect With a Mentor Commented:
*nod*  it's an interaction between ColdFusion and MySQL.

Basically, it's fine in MySQL, and it's fine in Access.  Something gets lost in the translation between MySQL and ColdFusion, and it doesn't get lost between Access and ColdFusion.

It may very well be a setting with MySQL, but I think there are more experts that use ColdFusion with MySQL than use MySQL with ColdFusion -- does that make sense?  So ask ColdFusion folks, if they say "yeah, you have to change this MySQL thing" then that's the answer, but it also might be in how it connects to ColdFusion, etc.
0
 
chigs20Commented:
First, what version of mysql are you using? And what character sets?
0
 
MichaelEvangelistaAuthor Commented:
mySQL 5 /  via NaviCat interface

Character set is "mySQL default"

thanks
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
chigs20Commented:
my guess is mysql isn't using uft-8 so coldfusion doesn't know how to interept non-utf-8 characters.  Try this:

1. Get in the CF Admin
2. Data Sources
3. Open the db
4. Click 'show advanced features' and in the Connection String type:  useUnicode=true&characterEncoding=utf-8


0
 
MichaelEvangelistaAuthor Commented:
Thanks... we did that just now... but no change...
http://thewholekaboodle.com.wehostwebsites.com/results.cfm?secondary=10
0
 
chigs20Commented:
Can you post:
Show variables like 'character%';
0
 
chigs20Commented:
I also noticed your application is using Latin 1 (iso-8859-1).  This is beginning to get out of the db scope but I strongly recommend you change that to UTF-8.

Yours:
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

Should be:
<meta http-equiv="content-type" content="text/html;charset=UTF-8">
0
 
MichaelEvangelistaAuthor Commented:
>> Show variables like 'character%';

Sorry, not sure what you mean.
This is a ColdFusion application, and everything worked perfectly in Access.
When i switched to mySQL is when the pound signs went wonky.
0
 
chigs20Commented:
MySQL has commands that show you the variables and health of the server.  If you issue:
SHOW VARIABLES LIKE 'character%';
It'll produce a list of characters that your database is currently using.

Characters are tricky, especially since you migrated from Access to MySQL.
This link: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
about 1/2 way down the page you'll see a bullet about character sets and changing them from one to another.  I recommend you read up and try what they propose.
0
 
MichaelEvangelistaAuthor Commented:
I can apparently change tables to UTF, but not the whole database (?)

When I run
SHOW VARIABLES LIKE 'character%';

I get mixed results

character_set_client  is latin1
connection, database, results, server are all latin1
character_set_system is UTF 8

Argh. Getting out of my comfort zone for sure.
How can I change the full database to UTF8?
0
 
chigs20Commented:
You can use the ALTER DATABASE CHARACTER SET UTF8...
see: http://dev.mysql.com/doc/refman/5.0/en/alter-database.html

0
 
MichaelEvangelistaAuthor Commented:
Aha... interesting, thanks.
I ran the SQL

ALTER DATABASE ***dbname***
    CHARACTER SET UTF8

and something did, in fact change...
now I have TWO question marks instead of just one in place of the GBP symbol!

Barring any further fixes, I think I am going to give up and leave this one in Access.
0
 
SheeriCommented:
If you see it OK in MySQL, then it's OK in MySQL.  It's ColdFusion that needs help.
0
 
MichaelEvangelistaAuthor Commented:
>> It's ColdFusion that needs help.

Well.. that was also part of my question.
=========
The product descriptions are entered through a simple text area form.
In the mySQL db, I can see the actual  £ sign in the text field
But when displayed on the page, it converts to ?
=========

If the answer is "this is definitely not a mySQL issue" then I will accept this answer.
The thing that got me going - in Access, the data looks fine AND displays fine. But when using mySQL, with no changes at all to the CF code or display pages, I have this problem.
Totally baffling.
0
 
MichaelEvangelistaAuthor Commented:
Ok... I will investigate further in CF-land.
Thanks for steering me off the wrong track, at least.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.