Foreign Character Display Problem From MySQL Database

Symptom
Result queried from MySQL database cannot show foreign character properly for example, in bunch of questions marks(???).

Cause
The collation of the database by default is in latin1_swedish_ci, hence the foreign character will have encoding issue.

Resolution
Case example: Chinese character

Solution for ColdFusion

First step.
Request Exabytes support to set the Character Encoding from Cold Fusion Administrator to

useUnicode=true&characterEncoding=utf8

Note:utf8(unicode) used to display Chinese character, depend on the collation of the database and table, apply the correct language code

Second step.
Edit your script.Add "SET NAMES 'utf8';" before connection to database.


Sample Script:

Script before edit

<cfquery name="GetItemData"
datasource="exabytescom_wordpress">

SELECT *
FROM wp_posts
ORDER BY post_content desc

</cfquery>

<cfoutput>
<cfcontent type="text/html; charset=utf-8">
<cfprocessingdirective pageEncoding="utf-8">

#GetItemData.post_content#
</cfoutput>

Script after edit

<cfquery name="GetItemData"
datasource="exabytescom_wordpress">
SET NAMES 'utf8';
</cfquery>

<cfquery name="GetItemData"
datasource="exabytescom_wordpress">

SELECT *
FROM wp_posts
ORDER BY post_content desc

</cfquery>

<cfoutput>
<cfcontent type="text/html; charset=utf-8">
<cfprocessingdirective pageEncoding="utf-8">

#GetItemData.post_content#
</cfoutput>

Solution for PHP

Edit your script.
Add "SET NAMES 'utf8';" before connection to database.


Sample Script:

Script before edit

<?php

// Make a MySQL Connection
mysql_connect("localhost", "username", "my_password") or die(mysql_error());
mysql_select_db("exabytescom_wordpress") or die(mysql_error());

// Retrieve all the data from table
$result = mysql_query("SELECT * FROM wp_posts ORDER BY post_content
desc")
or die(mysql_error());

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo $row['post_content'];
?>

Script after edit

<?php

// Make a MySQL Connection
mysql_connect("localhost", "username", "my_password") or die(mysql_error());
mysql_select_db("exabytescom_wordpress") or die(mysql_error());

mysql_query("SET NAMES 'UTF8'");

// Retrieve all the data from table
$result = mysql_query("SELECT * FROM wp_posts ORDER BY post_content
desc")
or die(mysql_error());

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo $row['post_content'];

?>

NOTE: if your php page is hosted in ColdFusion, you may need to to request Exabytes Support add useUnicode=true&characterEncoding=utf8 into data source of the database as mentioned.

Add Feedback