Solved

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

Posted on 2006-10-26
15
1,209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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
 
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

726 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