mySQL access to extended ASCII characters
Posted by: Shawn Taras
Date: February 04, 2005 01:47PM

I am currently running:

mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)

which, as I understand it does not support UTF-8 character sets. However, I only need to store extended ASCII characters which seem to be storing and displaying fine from within mysql. The problem I'm having is that the data getting to PHP is coming is some other encoding (ie. NOT UTF-8) so extended ASCII characters display as ?'s (question marks).

For example, if I use the following script to display a some of my columns:

<?php
$dbhost = "localHost";
$dbname = "myDB";
$dbuser = "myUser";
$dbpass = "myPassword";

$connection = mysql_pconnect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);
$result = mysql_query("SELECT * FROM myItems WHERE stringID=4");
echo '<pre>';
echo 'result: ' . $result;
while ($row = mysql_fetch_array($result, MYSQL_BOTH))
{
printf("\nID: %s:%s String: %s", $row["stringID"], $row["itemID"], $row["itemString"]);
}
mysql_free_result($result);
echo '</pre>';
?>

I get the following output:

result: Resource id #3
ID: 4:11 String: {IMG_B}Back
ID: 4:12 String: {IMG_B}Zur�ck
ID: 4:13 String: {IMG_B}Pr�c�dent
ID: 4:14 String: {IMG_B}Atr�s
ID: 4:15 String: {IMG_B}Indietro

If I change the print line in the PHP script to:

printf("\nID: %s:%s String: %s", $row["stringID"], $row["itemID"],
utf8_encode($row["itemString"]));

I get the correct output:

result: Resource id #3

ID: 4:11 String: {IMG_B}Back
ID: 4:12 String: {IMG_B}Zurück
ID: 4:13 String: {IMG_B}Précédent
ID: 4:14 String: {IMG_B}Atrás
ID: 4:15 String: {IMG_B}Indietro

which is great! The correct data is there, but the encoding that I'm getting is not displayable in the browser. The utf8_encode() function converts the encoding to utf8 which is properly displayed in the browser.

However, my final destination for my data is a Flash application using AMFPHP which requires that I send the $result to Flash from PHP. For example, here is one of the PHP methods to send data to Flash:

function getItem($stringID, $languageID)
{
if ($this->connection)
{
$result = mysql_query("SELECT * FROM myItems WHERE stringID=$stringID
AND languageID=$languageID");
return $result;
}
else
{
return false;
}
}

as you can see, I'm send the $result so I can't figure out a way to use the utf8_encode function on the strings within the $result. BTW: My flash application receives the data with the ?'s as well!

Is there anyway to parse and convert the data in the $result structure to utf8 before returning it to the Flash application?

If I upgrade to mySQL 4.1 where there is UTF-8 character set support, will this solve my problem? If so, is there any good documentation on how to safely upgrade? I am using Fedora Core 1.

Any other suggestions would also be welcome!

Thanks in advance!

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL access to extended ASCII characters
6876
February 04, 2005 01:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.