Multilingual Databasing Techniques, part 2

Multilingual Databasing Techniques, part 2
By Alex Buchanan

In my first article on multilingual databasing, I explained how to store and query foreign characters in a database. This follow-up will cover dealing with the se characters in an HTML environment.

- continued -

As you might have noticed in the first article, we took the query results and output them directly to HTML. This means that the computer who downloads this information will have to interpret these characters. This is fine if you're running a Windows PC in North America or if your application is confined to a controlled environment such as an intranet where you know what equipment is being used. There will be a problem once users from every location and platform will be able to access your application. These characters will be interpreted differently and could appear as gibberish on some systems.

The purpose of this article is to prevent this problem from ever occuring. There is only one solution: convert the foreign characters to HTML character codes (i.e. é, &ccirc;), which browsers can safely interpret, worldwide.

Only the ISO-8859-1 (Latin-1) character set is supported by HTML. Therefore, any other characters (cyrillic, asian, math symbols..) will require users to download additional character sets for their system.

I - Implementation
There are several ways to convert and display the foreign characters:

Create a separate field in the table with the converted characters.
Create a separate table (mapping a converted name to it's regular name).
Write a function which parses a string and converts characters as it finds them.

The first two ways follow the same principle as stripping accents in my previous article. In this case, it is definitely overkill to store HTML character codes in the database since a certain character code might change or not work properly in a certain environment. It would then be a pain to change it throughout the entire database. Instead I suggest using a conversion algorithm which will dynamically convert foreign characters at runtime.

Here is the function:

Function ConvertCodes(str)
  Dim i, letter, temp

  For i = 1 to Len(str)
    letter = Mid(str,i,1)

    If Asc(letter) > 122 Then
      temp = temp & "&#" & Asc(letter) & ";"
    Else
      temp = temp & letter
    End If
  Next
  
  ConvertCodes = temp
End Function

This function checks each character in a string and replaces foreign characters (having numeric values over 122) with their HTML character code equivalent. To use it in running code, it's simple. The following few lines of code will demon strate sample uses:

Response.Write ConvertCodes(oRs.Fields("Lname")) _ & ", " & ConvertCodes(oRs.Fields("Fname")) _ & " - " & oRs.Fields("PhoneNum")

Club president is: <% =ConvertCodes("Réjean Démanière") %><BR>

As you recall, the database holds data containing foreign characters. If we out put without converting, the foreign characters could be improperly displayed in certain environments, so we just add the ConvertCodes() function to the strings needing conversion. At every occurrence where you get output from a database which may contain foreign characters, insert the function.

Remember, one of the elements which makes a webmaster professional is the ability to produce websites and web content accessible and legible to anyone, no matter the browser, platform or location in the world.

II - Resources
HTML Character Code reference guides:

HTML Reference Library - Compendium of HTML information: tags, scripting, codes... available in a download able help file.
HotSource HTML Character Codes - Online listing of character codes.

III - Final Notes

I hope this article has been of help to you.

I strongly believe in the sharing of information and talent between professionals and amateurs alike. Should you know a better way of doing the same thing, please share your knowledge. I'm open to comments and criticism and can be emailed at alex@bh.qc.ca

Happy Programming!