Solved: apostrophe shows up as question mark

Create Account

MySQL Server

Questions

Followers

Top Experts

jimfrith

apostrophe shows up as question mark

I have a bunch of data in a table that has the wrong char set I guess because the apostrophe shows up as a question mark. If I open the record in phpmyadmin and delete the apostrophe and add it again it shows up correctly on my pages.

I tried this to update all the records: but it doesnt work

update MyTable set MyField = Replace(MyField, "'", "'");

but it doesnt work. it says updated 0 records. how do I identify the incorrect apostrophe?

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Guy Hengel [angelIII / a3]🇱🇺

if this query:
update MyTable set MyField = Replace(MyField, "'", "'");
says updated 0 records.
means that either your table has 0 records, or you forgot to show us the WHERE clause you used...

now, what is the character set of the mysql database?
what is the character set of you web pages (phpmyadmin)?

jimfrith

ASKER

No thats not the problem, the apostrophe is not being recognized as an apostrophe.

Guy Hengel [angelIII / a3]🇱🇺

>No thats not the problem,
well, can you please clarify:
* does your table have 0 records or not
* does your UPDATE statement have a WHERE clause or not?

>the apostrophe is not being recognized as an apostrophe.
again, the "issue" is for sure at the display, and NOT on the database level.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

jimfrith

ASKER

ok If I do a <? echo bin2hex($get_info[1]); ?>
I get 92 for the incorrect apostrophe and 27 for the correct one.

I tried this:

update MyTable set MyField= Replace(MyField, char(92), "'") ;

but i get an error
#1270 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'replace'

This returns the same error:
update MyTable set MyField= Replace(MyField, char(92), char(27)) ;

Guy Hengel [angelIII / a3]🇱🇺

what collation is your field MyField, please?

fill that in your update query:
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html

update MyTable set MyField= Replace(MyField , char(92) COLLATE latin1_swedish_ci , "'" COLLATE latin1_swedish_ci ) ;

Open in new window

jimfrith

ASKER

Well thats whats really confusing is in phpmyadmin it says the field is latin1_swedish_ci but when I do this

update MyTable set MyField= Replace(MyField , char(92) COLLATE latin1_swedish_ci , "'" COLLATE latin1_swedish_ci ) ;

I get this error
#1253 - COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]🇱🇺

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

jimfrith

ASKER

Hmmm same error

#1253 - COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'

Guy Hengel [angelIII / a3]🇱🇺

what is your server collation?

MySQL Server

Questions

Followers

Top Experts

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.