Solved

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

Posted on 2006-10-26
15
1,196 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
The purpose of this video is to demonstrate how to insert an Iframe into 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 : Open Page or Post…
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