Dealing with special characters in MySQL and PHP

[ggourde]

I have populated my database two differnet ways, and now I am seeing the data dealt with in two different ways.

First I was moving a database to mysql and I used the SQL sever on phpAdmin.  The result is that special characters show up correctly in the database, but incorrectly when outputed on to a webpage.

Second I created a form using Adobe Dreamweaver (CS4) forms. The result is that the special character do not look right in the database, but they do look right on the webpage.

Any thoughts on how to synch these?

[Ben M]

First, I see two blank boxes in your post. What was supposed to be in them? Can you repost?

Second, are you storing your special characters as HTML entities? Can we see an example of the data you are trying to store?

[ggourde]

Oh, my pics didn't make it:

[Ben M]

Going back to my second point you need to store those special characters as HTML entities meaing: the "&" becomes "&".  See the following PHP page for reference.

http://www.php.net/manual/en/function.htmlspecialchars.php

[ggourde]

So then you're saying that in the database they are supposed to look messed up (HTML entities)? So the pink circled ones in mysql are appearing correctly and the yellow circled ones had been entered incorrectly?

[Ben M]

So then you're saying that in the database they are supposed to look messed up (HTML entities)?

Yes absolutely.  Not all languages/characters/etc translate so storing the entities is the safest way to prevent erratic results like you are experiencing.

So the pink circled ones in mysql are appearing correctly and the yellow circled ones had been entered incorrectly?

Actually I'm saying neither one is correct.  An HTML entity is not that strange characters you have in the database.  They are usually denoted by "&xyz;" where the "xyz" is the entity and the & begins and ; ends.  This is a full list of entities ( http://www.w3schools.com/tags/ref_entities.asp ).

I don't know how the characters in the purple ones are translating into HTML at all.  By all accounts it shouldn't.  Basically all you need to do is run the variable through that function before uploading into a database like:

$var = htmlspecialchars($var);

The resulting $var will have the entities in there and stored in the database.  Then the browser can render the entities on output.