[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-10-26
15
Medium Priority
?
1,235 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Suggested Courses

872 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