asked on

PHP/MYSQL - Insert degree Symbol

I am trying to update a field, and the query looks like this:

UPDATE form_fce_his SET `history_medications`='test 2323°' WHERE id=26

However, what gets sent to the mysql server is this:

UPDATE form_fce_his SET `history_medications`='test 2323Â°' WHERE id=26

Any idea what might be causing this? I checked the query right before it was sent to the server, and it was correct. However, if I email myself that same query, I get the bad one, like what is sent to the server. At some point, it seems that PHP is modifying it, but I don't know where, or how to fix it. I can send the correct SQL to the mysql server manually, and it works fine.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

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

ASKER

Ok, but that still doesn't explain why if I echo the SQL to the screen, I get it displayed correctly, but if I email mysql the sqL (part of our debugging code), I get the incorrect one.

SOLUTION

membership

Create a free account to see this answer

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

ASKER

I understand about character sets, but this still doesn't add up. Let me test this and see.

ASKER

PHP isn't set to use a default character set in the php.ini file. What would it default to, if it's not set?

it would then default to your OS default.

ASKER

So, how can I find out what that is? I have been unable to find a PHP function that tells me what the current character set is.

well, what is your operating system and what language?

ASKER

Windows Server 2003, IIS 6, English

what country?

ASKER

USA

then your default character set is either US-ASCII or ISO-8859-1

consequently, you would lose that character if your MySQL DB is in a different character set.

ASKER

Well, I thought it was ISO-8859-1, but that character set is not available in MYSQL (at least it's not shown when I do SHOW CHARACTER SET.)

you can use iconv.

ASKER

That's a lot of work to just make the degree symbol work. I'm surprised that by default PHP and MYSQL are not in the same encoding.

it's not an issue of PHP/MySQL. It's really more a problem of how you set things up.

ASKER

How do you figure? I set everything up with the defaults, that's what I mean by the issue. Nothing in PHP or MYSQL installation documentation says that if you want to use this with PHP (which most people do), you need to make sure you are using like character sets. And in fact, that the default character set for my region, does not exist in MYSQL, or so it appears. I've been using PHP and MYSQL for 7 years, and this is the first time I've had this problem occur. Strange that it's only with this particular symbol that I'm having the problem with too. Anyway, thanks for you help.

ASKER

I found out that latin1 is the equivalent to ISO-8859-1 for MYSQL. I set it as you described for PHP, using mysql_set_charset('latin1'), but it did not fix my problem.

ASKER

I guess I should have tested it before I closed the question. :)

° is not in the latin-1 chart if i am not mistaken. It's an 8-bit chart.

ASKER

I found references on the Internet that say that is.

yes, admittedly iso-8859-1 does have a degree sign at character B0. But the source of your problem is still an encoding problem US-ASCII is only a 7-bit character set and could definitely cause this problem.

ASKER

Ok, so how do I set MYSQL to ISO-8859-1?

FYI, if I store ° in the MYSQL table, PHP will return a degree symbol.

if you store ° in MySQL, you get ° back in PHP. It displays as a degree sign on a web page. (the distinction is meaningful and important). if you are actually getting a degree sign in the database rather than °, then something is wrong with your query function and it is not sending the queries that you ask it to send.

what encoding is the field presently set to?

ASKER

I didn't think that was what was happening, but it is (strange that that happens even in an input box). Field encoding for all fields is latin1.

ASKER

After further review, the database versus PHP character set is NOT the problem. I was able to finally see exactly what was being sent to the server (attached as jpg, pulled from JS alert.)

This is being done with Ajax. Does that make a different? Anyway, the Â is being added before it ever hits the MYSQL database.
sql.JPG

submission via AJAX may or may not make a difference. What is meant by AJAX in this context ...
(a) hidden IFRAME?
(b) asynchronous XML?

if (a), then it is no different from a normal form submission.
if (b), then there are conversion that happen and convert many characters to entities.

but Â is Â

so I am not sure why that would be submitted at all...

ASKER

B.

I did some more testing and I'm not sure where it is converting, but I know that if I perform htmlentities() on this data:

2323°

It gets converted to

2323Â°

Why is that?

ASKER

FYI, I was NOT using htmlentities() when we started this, but getting the same result in the database.

I also get the &Acirc as output when i just cut and paste your string.

this thread: http://sourceware.org/ml/docbook-apps/2002-q2/msg00683.html seems to be on the same issue.

ASKER

That fixed the problem. We do internal development only, and so I did not set the page CONTENT-TYPE. Once I set it to UTF-8, it worked. Thanks.

ASKER

Ok, I lied. Darnit! What worked is I set it to UTF-8, and then stripped the Â from the it (I forgot I had added that code.)

ASKER

Well, the UTF-8 did not do anything, actually. I'm just going to strip the other character out for now.