Databases, character encoding, PDFs and XML

Question

I am having little trouble with character encoding.

The situation

A file is uploaded that is converted into XML. The character encoding of this file varies, however smart quotes, entities and, various ASCII may appear. Once this file is converted into XML it is stored in a database. Upon user request, the XML may extracted from the database and converted into an array where it is then created into a PDF.

The problem

Character Encoding. Right from the beginning, character encoding has played a major issue. I would like to know;

What character encoding generally covers the entire "spectrum". For instance, a ° which is unrecognised when parsing the XML or a smart quote (’). The smart quote will turn into â€™, etc, etc.
How to store XML in a database. Encryption is a possibility, however database encoding is where I am getting lost at.
How to get the entities, smart quotes (and other characters which may cause a problem) to appear correctly in a database and with a Å in front of stuff.

Attempts at a work around

I have made various functions which "attempt" to solve my problem - converting some characters into another. However, I assume this is the entirely wrong way of doing it and I should be changing the character encoding.

/*
 * Converts smart quotes to ascii
 */
function convert_smart_quotes($string) {
    $string = iconv("UTF-8", "UTF-32", $string);
    $string = mb_convert_encoding($string, 'HTML-ENTITIES', 'UTF-32');
    $string = str_replace('', '', $string);
    $search = array('‘', '’', '“', '”', '—'); 
    $replace= array("'", "'", '"', '"', '-');
    $string = str_replace($search, $replace, $string);
    return $string; 
}
/*
 * Converts some entities to an ISO format?
 * 
 * Example  : ° => °
 */
function entity_to_iso($string) {
    return html_entity_decode($string, ENT_QUOTES & ~ENT_COMPAT, 'ISO-8859-1');
}

Ultimately, my problem resides in the fact that I do not know the encoding of the file that is uploaded. I had an idea of a switch that attempts to convert characters into something more database and "PDF friendly". However, much Googling has resulted in bitter work arounds or arrays that str_replace one thing to another. Is this really the solution?

Any advice, solutions or fingers pointed in a better direction are all helpful and much appreciated. Thank you.

Databases, character encoding, PDFs and XML

2 Answers