Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How can I convert a database to support UTF-8 and convert it's old data from what ever encoding they're in to UTF-8 ?

Extra Info: I'm running a server which has many websites on it, and one of them is running WHMCS (php script to manage hosting clients). WHMCS has an iPhone application where i can browse it through iPhone, the problem is that this application will only run if everything in my website is in UTF-8 encoding.

I was using windows-1256 as encoding in my script's settings, and i tried changing that in some point of time to UTF-8 for a while then changed it back to windows-1256

so, the data in the database are some inserted using UTF standards and most of them are windows-1256

If someone could clear the picture for me, Do I need to convert every database on the server or just one DB ? what should I change?

If i had to do that manually, I'll do it but I need some expert advise.

share|improve this question
    
I'd like to help you, but you need to give more details. You don't say what software you are using. I'm guessing you're MS since MS users are typically unaware of anything else. You seem to be having questions related to a specific commercial software package. You should ask them for support. –  d-_-b Jun 29 '10 at 9:12
    
Thanks for replying sims, Mentioned before, the software is WHMCS (www.whmcs.com). If you mean system software, they are MySQL5.* +PHP on a centOs 5 64bit box. I only included what I thought is relevant and saved the rest to the need! And Yes I'm an MS user but I'm pretty aware of what I'm using. –  Mohannad Otaibi Jul 5 '10 at 8:32
add comment

2 Answers

Database encoding is normally defined when you create the database (certainly for Postgres). One way to fix this is to export, create a new DB an then re-import. This link may be enough.

However the encoding of the application can be set different than the database. This means that the application send out UTF-8 and when it read the DB it converts the DB encoding to UTF-8. This is normally done on the connection to the database (see this PHP function).

So if you have access to the source code (should be the case) find where the app does the connection and set the encoding to you DB code base. Then find where the app or Apache set the output encoding and set it to UTF-8. This should do the trick.

share|improve this answer
add comment

Since WHMCS is a commercial application, you should contact the vendor for support on how to convert the DB contents from the existing encoding to UTF-8 - particularly since not all applications work well with UTF-8 data (sorting/collation and searching are often affected). MySQL and CentOS may require some configuration, while PHP probably won't since it doesn't directly support UTF-8, though PHP applications can use UTF-8.

Converting an existing application to UTF-8 is not a trivial exercise, though it should be possible with some support.

There might be a simpler option such as writing a filter for use only when the iPhone is using the page. If you can guarantee there are no 8th-bit-set characters anywhere in the application UI or the database (i.e. ASCII only) you might be able to simply set the encoding to UTF-8 on output, but that could be risky.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.