Solved

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

Posted on 2006-10-26
15
1,166 Views
Last Modified: 2013-12-16
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.

0
Comment
Question by:MichaelEvangelista
  • 7
  • 6
  • 2
15 Comments
 
LVL 6

Expert Comment

by:chigs20
ID: 17815624
First, what version of mysql are you using? And what character sets?
0
 

Author Comment

by:MichaelEvangelista
ID: 17815655
mySQL 5 /  via NaviCat interface

Character set is "mySQL default"

thanks
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17815721
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
 

Author Comment

by:MichaelEvangelista
ID: 17815911
Thanks... we did that just now... but no change...
http://thewholekaboodle.com.wehostwebsites.com/results.cfm?secondary=10
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17821061
Can you post:
Show variables like 'character%';
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17821078
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
 

Author Comment

by:MichaelEvangelista
ID: 17822625
>> 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 6

Expert Comment

by:chigs20
ID: 17822669
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
 

Author Comment

by:MichaelEvangelista
ID: 17829191
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
 
LVL 6

Expert Comment

by:chigs20
ID: 17829419
You can use the ALTER DATABASE CHARACTER SET UTF8...
see: http://dev.mysql.com/doc/refman/5.0/en/alter-database.html

0
 

Author Comment

by:MichaelEvangelista
ID: 17829494
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
 
LVL 4

Expert Comment

by:Sheeri
ID: 17868684
If you see it OK in MySQL, then it's OK in MySQL.  It's ColdFusion that needs help.
0
 

Author Comment

by:MichaelEvangelista
ID: 17869105
>> 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
 
LVL 4

Accepted Solution

by:
Sheeri earned 500 total points
ID: 17869244
*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
 

Author Comment

by:MichaelEvangelista
ID: 17869298
Ok... I will investigate further in CF-land.
Thanks for steering me off the wrong track, at least.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now