Development: MySQL: UTF-8 compatibility bug

Hi all,
Simple Invoice uses UTF-8 as a default encoding. That's great. But if you save any accented character (á, é, í, ó, etc.), you can see in the database admin (phpMyAdmin for example) that these characters are stored in wrong way - for example é is stored as á, á is stored as á etc. That's wrong. The reason is that most of the MySQL installations don't use UTF-8 as a default encoding. It's a latent bug, because in the output the characters are displayed well (they are encoded back to UTF-8 entities). The problem is that MySQL does not recognize that the text is UTF-8 encoded, so it stores these characters as HTML entities (á) or as two separacte characters instead of one two-byte UTF-8 character. This could cause errors in indexing, searching, etc.

Solution:
MySQL needs to know the character set of the connection.
More info: http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html

So in this case, I put this in sql_queries.php:

$conn = mysql_connect( $db_host, $db_user, $db_password,true );
$db = mysql_select_db( $db_name, $conn );
$mysql = mysql_get_server_info(); //mysql_version
mysql_query("SET NAMES utf8"); <--------------

Warning: if you apply this fix in your existing installation, your existing data (accented characters) will be displayed damaged.
They should be fixed manually or by some fixing script.

Regards,
Robert

just tested it - your right mysql doesnt store those characters correctly

question: if you do a PDF for an invoice with those characters does it come out OK?

below is the PDO code - i'll include this in the next release if testing is OK

$connlink = new PDO(
$pdoAdapter.':host='.$config->database->params->host.'; dbname='.$config->database->params->dbname, $config->database->params->username, $config->database->params->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);


cheers

justinSmarterInvoices.com Simple Invoices hosting | SixHQ.com Simple Invoices custom development | Me

Thanks Robert

I've set those 2 properties in our demo site http://www.simpleinvoices.org/demo

if you can login and let me know if all is OK it would be great - also try doing some PDFs - we've had lots of problems in the past with non-latin and PDF

Cheers

JustinSmarterInvoices.com Simple Invoices hosting | SixHQ.com Simple Invoices custom development | Me

I also tested new PDF system. It is really great. In Turkish we have 7 special characters and as i tested now 4 of them are working but still 3 character are not shown properly.
See the Invoice 586 for problem.

Working characters are: çöiü
not working characters are: ???
edited: (how can i write them it is also not working in the post)

Sorry it is my mistake. I now realized that my template which i am already using for months has do not have any problem about Turkish characters. (I am using Beta 1)

But now i am begin to afraid. will it be a problem in the next version?