Might be PHP after all

I found the mysql group on google and posted a question (which I'll attach
below). On thinking about it, it might be a php problem after all. I can
see the data properly via phpMyAdmin (an apostrophe is an apostrophe).
However, on looking at the retrieved results in php, the apostrophe takes on
an odd three character sequence. Also, in looking at the text file in
MSWord, I see apostrophes, but they look different than the apostrophe used
to delimit a field value. When I try to do a replace on the apostrophes I
want, it picks them all out to replace.

So, this might be php after all.

(Side note to Jerry: The activity in that group is about 30 posts/replies
per day, with two of them from you. Not a very active group -- but
passable).

Here is what I posted there:

************
I have to port data from MySQL 4.1.x to MySql 4.0.27 (The client is
changing hosts). Call these S for source and D for destination.

On D, if I put in data with an apostrophe (using addslashes), it goes
in fine and comes out fine. However, data from S which is now in D
via an export/import (done already, but I can redo it if needed) when
retrieved via PHP comes up with a strange sequence of âEURO(tm) instead of
an apostrophe. Also, the e with the back accent mark as in Café comes
out CafÃ(c),

S has a default character set of lantin1, while D has a character set
of utf8.

I have tried an "alter table foo character set latin1" on D. No
help. I have then gone and put it back to utf8. Nothing changes. I
have also tried exporting from S specifying it as utf8 and importing
it on D. No luck there either.

When I go into phpMyAdmin and look at the data, I see an apostrophe.

Short of digging through all the data and doing an update, or looking
at the export from D, massaging it, and reimporting it, I don't know
how to fix these data.

Any help is welcome.
***********

With a followup of:

**********
One more thing. When I look at the text file that was exported from
S, I see apostrophes, yet when I then import it to D and look at it I
see the junk. (I created a new table and did this for testing
purposes).
*********

--
Shelly

Re: Might be PHP after all

> I have to port data from MySQL 4.1.x to MySql 4.0.27 (The client is
> changing hosts). Call these S for source and D for destination.
>
> On D, if I put in data with an apostrophe (using addslashes), it goes
> in fine and comes out fine. However, data from S which is now in D
> via an export/import (done already, but I can redo it if needed) when
> retrieved via PHP comes up with a strange sequence of âEURO(tm) instead of
> an apostrophe. Also, the e with the back accent mark as in Café comes
> out CafÃ(c),
>
> S has a default character set of lantin1, while D has a character set
> of utf8.

OK. Let's anlyse this. What you are seeing if utf-8, but rendered as
latin-1. It is a real pity that you have to backport data, as encoding
support only gets better with the versions. Therefore, getting back
makes things worse. Your best bet is to adapt the database in the most
recent version of the two and then backport it.

But if you get utf-8 anyway (even if rendered incorrectly), I suppose
this went well. You can always check by using the mysql_dump utility and
see what strings it generates. This is one of the few reliable methods
to see how the strings are stored.

So what is wrong is that you see data in utf-8, but your client does not
know it.

Take a look at my.cnf (your MySQL config file). It contains a section
[mysql], a section [mysqld] and a section [client]. Check if the client
section has its "character set" option set to utf-8.

>
> I have tried an "alter table foo character set latin1" on D. No
> help. I have then gone and put it back to utf8. Nothing changes. I
> have also tried exporting from S specifying it as utf8 and importing
> it on D. No luck there either.
>
> When I go into phpMyAdmin and look at the data, I see an apostrophe.
>
> Short of digging through all the data and doing an update, or looking
> at the export from D, massaging it, and reimporting it, I don't know
> how to fix these data.


So far, it could be a mysql issue. But you have not told us what
application renders the query results. If you have a web page that just
tells the browser to render "text/html; charset=iso-8859-1" and send
strings in utf-8, it is clear that you will get the above output. In
that case, it is a PHP problem.

It can be solved by sending the correct encoding. This can be done in
PHP.INI (server-wide, or from an apache config section-wide), or just by
sending the appropriate Content-Type header "by hand".

Good luck!

Re: Might be PHP after all

On Thu, 15 Nov 2007 15:50:50 +0100, Shelly
wrote:
> I found the mysql group on google and posted a question (which I'll
> attach
> below).

Such impatience...
--
Rik Wasmus

Re: Might be PHP after all

Dikkie Dik wrote:
>> I have to port data from MySQL 4.1.x to MySql 4.0.27 (The client is
>> changing hosts). Call these S for source and D for destination.
>>
>> On D, if I put in data with an apostrophe (using addslashes), it goes
>> in fine and comes out fine. However, data from S which is now in D
>> via an export/import (done already, but I can redo it if needed) when
>> retrieved via PHP comes up with a strange sequence of âEURO(tm)
>> instead of an apostrophe. Also, the e with the back accent mark as
>> in Café comes out CafÃ(c),
>>
>> S has a default character set of lantin1, while D has a character set
>> of utf8.
>
> OK. Let's anlyse this. What you are seeing if utf-8, but rendered as
> latin-1. It is a real pity that you have to backport data, as encoding
> support only gets better with the versions. Therefore, getting back
> makes things worse. Your best bet is to adapt the database in the most
> recent version of the two and then backport it.
>
> But if you get utf-8 anyway (even if rendered incorrectly), I suppose
> this went well. You can always check by using the mysql_dump utility
> and see what strings it generates. This is one of the few reliable
> methods to see how the strings are stored.
>
> So what is wrong is that you see data in utf-8, but your client does
> not know it.
>
> Take a look at my.cnf (your MySQL config file). It contains a section
> [mysql], a section [mysqld] and a section [client]. Check if the
> client section has its "character set" option set to utf-8.
>
>>
>> I have tried an "alter table foo character set latin1" on D. No
>> help. I have then gone and put it back to utf8. Nothing changes. I
>> have also tried exporting from S specifying it as utf8 and importing
>> it on D. No luck there either.
>>
>> When I go into phpMyAdmin and look at the data, I see an apostrophe.
>>
>> Short of digging through all the data and doing an update, or looking
>> at the export from D, massaging it, and reimporting it, I don't know
>> how to fix these data.
>
>
> So far, it could be a mysql issue. But you have not told us what
> application renders the query results. If you have a web page that

The application is the web application that gets the data from the database
and shows it. It is obtain with a mysql_query and the succeeding commands.

> just tells the browser to render "text/html; charset=iso-8859-1" and
> send strings in utf-8, it is clear that you will get the above
> output. In that case, it is a PHP problem.

It is "text/html; charset=iso-8859-1". So it IS a php problem?

> It can be solved by sending the correct encoding. This can be done in
> PHP.INI (server-wide, or from an apache config section-wide), or just
> by sending the appropriate Content-Type header "by hand".

Changing Content-Type would do it? I'll look up what kind I need, but if
you would be so kind.....?

Shelly

Re: Might be PHP after all

Thank you Dikkie. Changing the header content type in the metadata to utf8
solved the problem. It displays properly now.

Rik, thank you as well for posting here and on comp.databases.mysql.

--
Shelly

Re: Might be PHP after all

> It is "text/html; charset=iso-8859-1". So it IS a php problem?
>
>> It can be solved by sending the correct encoding. This can be done in
>> PHP.INI (server-wide, or from an apache config section-wide), or just
>> by sending the appropriate Content-Type header "by hand".
>
> Changing Content-Type would do it? I'll look up what kind I need, but if
> you would be so kind.....?


just send a header "Content-Type: text/html; charset=utf-8"

I think it is time to read something about the subject:
http://www.joelonsoftware.com/articles/Unicode.html

Good luck with the onions.

Re: Might be PHP after all

Dikkie Dik wrote:
>> It is "text/html; charset=iso-8859-1". So it IS a php problem?
>>
>>> It can be solved by sending the correct encoding. This can be done
>>> in PHP.INI (server-wide, or from an apache config section-wide), or
>>> just by sending the appropriate Content-Type header "by hand".
>>
>> Changing Content-Type would do it? I'll look up what kind I need,
>> but if you would be so kind.....?
>
>
> just send a header "Content-Type: text/html; charset=utf-8"
>
> I think it is time to read something about the subject:
> http://www.joelonsoftware.com/articles/Unicode.html
>
> Good luck with the onions.

Thanks again. I found and did that on my own after your suggestion of
content type, but thanks for responding with the answer.

--
Shelly