[Bug] MySQL issue and a temporary solution

[QmQ]

I hope this thread might be helpful to some friends suffering the same problem .
[Problem]
These days, I updated my pixelpost to 1.7.1 and also moved to a new MySQL server
running MySQL 5.0.45 from a server running MySQL 4.0.
Then I found titles, comments which based on UTF-8 encoding are totally mussy.
The strange thing is that all the stuff are shown correctly when i view the MySQL
database with mysqladmin, but I got mussy thing when i visit my blog with internet browsers.

[Solution]
1.Open includes/functions.php
2. Search "function start_mysql", then you will find it at about Line 492.
3. go to the end of this function, add the following line:
mysql_query("SET CHARACTER SET 'utf8'"); //added by QmQ
4. save,upload and overwrite the old function.php file.


[Explanation]
The reason is that the default character set's setting of MySQL is a little tricky.

Use the following SQL command, you are able to see your setting:
SHOW VARIABLES LIKE 'character_set_%';
for example, this is my setting
================================
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
=================================
Here, you can see the character_set_server is "latin1", not utf8.
And "character_set_server" is the default character set for a connection to database.
That is the reason we got some strange characters--pixelpost will use "latin1" to
connect to a utf-encoding database.

So, we got the reason. However, I don't have right to change my character_set_server to utf8.
So I added some codes to use utf8 as the character_set when pixelpost
connecting to the database -- It will "tell" pixelpost to use utf8 as the default
encoding.

[Dennis]

Thanks for providing this fix. I hope this will be the solution to many problems related to encoding.

[Dkozikowski]

QmQ, your solution is not temporary, this is a permanent solution as it is the correct way to set your databases encoding type when you cannot do so otherwise.
Future versions of Pixelpost may come with this along with a configurable option to set the blogs site/db encoding site-wide. Until then, continue using your modification.

[QmQ]

Thanks, I think we can add a configurable option in pixelpost.php -- just like wordpress did in their new version
e.g.:
$pixelpost_db_character_set

[Dkozikowski]

Yeah, something along those lines.

[Günter]

I would highly recommend to use the built-in function of php:

http://de2.php.net/manual/en/function.mysql-set-charset.php

as it is the recommended way. Alternately you should do:

SET NAMES 'utf8'
SET CHARACTER SET 'utf8'

according to http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

any roadmap for this? seems to be already one year old..