?
Solved

MySQL turns apostrophes into symbols?

Posted on 2007-11-29
7
Medium Priority
?
4,520 Views
Last Modified: 2013-12-12
I am using, as per my understanding, MySQL version 5.0.45-community.

Using a PHP script, I am retrieving text from a database after inputting it with phpMyAdmin. This text contains apostrophes (the keyboard character, not the ASCII code version).

When that text is outputted to a web page, it appears as a symbol (in Firefox, as a question mark inside a diamond) instead of an apostrophe.

What is causing this, and how can I fix it?
0
Comment
Question by:TheAnarchist
[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 Comments
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 20380678
Are you using the proper charset to connect to DB and in webpag? What you see when you view source?
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 20380681
I am talking about HTML source in output webpage.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 1200 total points
ID: 20380690
is it a unicode string field? either that or it's a charset issue. in your html header, specify the correct charset
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 21

Expert Comment

by:nizsmo
ID: 20380714
Try when inserting the data with the apostrophe to use either:

$escaped = addslashes($myvalue);

or

$escaped = mysql_escape_string($myvalue);

and see if it works when retrieving.

Hope this helps.
0
 
LVL 13

Assisted Solution

by:rhickmott
rhickmott earned 300 total points
ID: 20380729
Its because firefox is recognising it as a reserved character.

Depending on the scripting your using ( HTML,XHTML ( Transitional, Strict, Etc )).

IE has a "Quirks" mode which will display quotes etc BUT because there used in the HTML language they are normally need to be converted to explicitly tell the browser to display the character rather than interpret it as part of a tag. firefox tends to convert most things £ signs, Aphersands etc into the diamond question mark if you dont print it correctly.

For this reason you need to convert "'s to &quots; &'s into & and so on.

If you view your source you will find that the right character is there.... Its just firefox wont display it.

To get around this problem you can use the htmlentities function ( http://uk.php.net/htmlentities ) to convert special characters into displayable ones. Its worth noting you should do this on all data from a database you wish to display and all incoming data to make sure that the user hasent put "> into a textbox etc otherwise when you display the results it will become part of the html which breaks things significantly. If your putting stuff into the database from a user you should make sure that no ' and " exist as it can lead to SQL injection attacks. Once you have the input you can use the reverse function and addslashes etc for inserting into your database.
0
 
LVL 6

Author Comment

by:TheAnarchist
ID: 20417090
My apologies for not getting back to this question yet. A number of technical issues and other unrelated problems have been coming up.

I will resolve this question, hopefully before the end of next week.
0
 
LVL 6

Author Comment

by:TheAnarchist
ID: 20424989
Thanks to all for your help. I have split the points.

The issue at hand turned out to be with the character set. The HTML page (which was built by another developer) used UTF-8 encoding. Changing the character set to an ISO character set solved the issue of the question marks.

imitchie gets 400 points for solving the issue at hand.

rhickmott, gets 100 points for the detail provided in their answer (and because it has solved a seperate issue that I will need to consider further down the line). As it turned out, the question mark symbol was visible in the source as much as it was on the page - changing the character set resolved this issue, however.

Thanks again.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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