How many times have you been presented with a character encoding glitch on a PHP site that you thought was working properly? You finished the site months ago with no issues, but now you are being told some weird character is showing up on that page? Chances are, that character is either Ã, Â or Ä, possibly followed by some other random character. If I’m right, keep reading. If not, I offer my deepest condolences, for you are knee deep in a character encoding issue that I cannot help you with.

So what is it that causes that à to show up? In short – you have UTF-8 encoded data set to display as ISO-8859-1. But the more important question is how to prevent this from happening in the future. The easy answer to that – store all data in the same format as the final display. Convert the encoding as soon as you receive the data, either through an input field, reading a file or any other method. Don’t put anything in your database unless you are sure it is in the right format.

First, an introduction to the three important character encodings that you are likely to deal with – ASCII, UTF-8 and ISO-8859-1. Character encoding is, in a nutshell, the exact method that characters are stored and displayed by a computer. Since computers only deal in binary, there must be some way to convert from binary into a character. Character encodings come in two flavors (at least, two flavors I’ll be discussing) – single byte and multi byte encodings. Single byte encodings, such as ASCII and ISO-8859-1, use a single byte to represent a character. For instance, the letter “M” in ASCII is represented as 0×4D, whereas the number 4 is 0×34.

A single byte is limited to only 256 possible values, which rapidly becomes a limitation when you want to use characters not typically seen in English, such as “ç” or “ß”, and when you begin thinking about Asian languages, you can quickly see there simply isn’t room for all characters. Multi byte encodings use one or more bytes to represent a character. Each byte still only has 256 possibilities, but 2 bytes have 65,536 possibilities. But, what if there is confusion as to whether or not you are using single or multi byte encoding? Is that short string “0×3C 0xA5″ supposed to be the UTF-8 character å or the ISO-8859-1 string ÃÑ? And there we are – a sudden bug has shown up.

ASCII is the oldest of the three and the most basic. The ASCII character set started out as just 128 characters, and very English-centric. You can see that the basic ASCII table is very restricted to the English speaking world. The extended ASCII table goes off into some weird directions and is rarely used. The full ASCII table is only 256 characters, and thus fits neatly into a single byte, and so it is a single byte encoding.

ISO-8859-1 is also a single byte encoding, but you can see that it is a bit better in the extended range to support many Latin based languages. Unlike ASCII, ISO-8859-1 can handle the majority of Latin based languages without too much issue. It fails when you go outside of the Latin based languages, however.

The third character encoding is UTF-8, which is designed to support over 1.1 million characters, more than enough for all existing languages (and a few dead ones). Unlike ASCII and ISO-8859-1, UTF-8 is not limited to a single byte, but can vary between 1 and 4 bytes, depending on the character being used.

One thing to take note of is that the first 128 characters of all three encodings are identical. This is why a character encoding issue on an English website can take so long to spot. You can go months not knowing that you have a character encoding issue because the only characters you used were the same in all 3 encodings – 0×4D is an M in ASCII, UTF-8 and ISO-8859-1.

On to the meat of this article – dealing with character encodings in PHP. Namely, how to detect and convert encodings.

The magic bullet of dealing with character encodings in PHP can be summed up in three functions – mb_detect_encoding(), mb_detect_order() and iconv(). The mb libraries are designed for working with multibyte languages and iconv is a function to convert from one encoding type to another. One nonobvious restriction to the multibyte languages is the lack of native support for ISO-8859-1, since that is a single byte language. mb_detect_order() can be used to specify the order of encodings to scan when attempting to detect an encoding on a string.

The basics of dealing with character encoding conversion in PHP is a simple three step process:

1. Set the encoding types to scan (ASCII, UTF-8, ISO-8859-1). Luckily, this only has to be done once.
2. Identify the encoding of the string to convert.
3. Convert to the desired encoding type (I prefer UTF-8).

But, let’s cut to the crap. Sample code!

// tell the multibyte library which encodings to use
mb_detect_order('ASCII, UTF-8, ISO-8859-1');

// tell the browser that we are using UTF-8
header('content-type: text/plain; charset=UTF-8');

$string = "Fün wîth èñcøding!";

// UTF string displays properly
echo $string . "\r\n";

// convert to ISO-8859-1, which will confuse the browser
$string = iconv(
    mb_detect_encoding($string),
    'ISO-8859-1',
    $string
);
echo $string . "\r\n";

// convert back to UTF-8, which the browser understands
$string = iconv(
    mb_detect_encoding($string),
    'UTF-8',
    $string
);
echo $string . "\r\n";

While working on some code recently, I realized that mysql_insert_id fails when using insert ignore. When using insert, mysql_insert_id returns the primary key of the newly inserted row. However, nothing is returned with insert ignore if a key conflict prevents a record from being inserted. If you are wanting to get the key that a conflict was just hit against, in as dynamic of a way as possible, you can use this script to find the primary key when insert ignore does not enter a record. This is useful if you are setting up a many-to-many pivot table and don’t want duplicate data on either side. When you attempt to insert a new record, it either gives you the new key or the key of the one that already existed.

$db->query($sql)

// if an insert id exists, use it
if ($db->insert_id != 0) {
    $id = $db->insert_id;
// if there is no insert id and there was no error and insert ignore was ued
} elseif($db->insert_id == 0 &&
    empty($db->error) &&
    preg_match('/^\s*insert\s+ignore/si', $sql)) {

    // find the table that was queried
    preg_match('/^\s*insert\s+ignore\s+into\s+([-`a-zA-Z0-9_]+)/si', $sql, $extract);
    $table = trim($extract[1], '`');

    // change insert ignore  to insert
    $Sql = preg_replace('/^\s*insert\s+ignore/si', 'insert', $sql);

    // query and scan the error for the key conflict
    $db->query($sql);
    $error = $db->error;
    preg_match('/^Duplicate entry \'(.*)\' for key (\d+)$/', $error, $extract);

    $value = $extract[1];
    $key = $extract[2];

    // in the case of multi column keys, figure out what the keys actually are
    if(strstr($value, '-') && !strstr($sql, $value)) {
        $values = explode('-', $value);
        $finished = false;
        while(!$finished) {
            foreach($values as $k=>&$v) {
                if(strstr($Sql, $v.'-'.$values[$k+1])) {
                    $values[$k] = $v.'-'.$values[$k+1];
                    unset($values[$k+1]);
                    $values = array_values($values);
                    break;
                }
                $finished = true;
            }
        }
        $value = $values;
    }

    // look up all keys on the table, isolating the primary key
    $keySql = "show keys from `$table`";
    $keyResult = $db->query($keySql);
    $keys = array();
    while($row = $keyResult->fetch_assoc()) {
        if(strtolower($row['Key_name']) == 'primary') {
            $primary = $row['Column_name'];
        }
        $keys[$row['Key_name']][] = $row;
    }

    // build a where clause to find the primary based on key conflicts
    $keys = array_values($keys);
    if(!is_array($keys[$key-1])) {
        $unique = $keys[$key-1]['Column_name'];
        $where = "`$unique` = '".$db->real_escape_string($value)."'";
    } else {
        foreach($keys[$key-1] as $key) {
            $whereParts[] = "`{$key['Column_name']}` = '".$db->real_escape_string($value[$key['Seq_in_index']-1])."'";
        }
        $where = implode(' and ', $whereParts);
    }

    // get the primary key that conflicted with the insert
    $sql = "select `$primary` from `$table` where $where";
    if(is_object($result)) {
        $result = $result->fetch_assoc();
        $id = $result[$primary];
    }
}

echo $id;