I've been searching the web for hours and nothing I find seems to help. This was working fine when I was on PHP 5.3 and MySQL 5.1. When I upgraded both of these, I realized that selects done against fields with UniCode characters in them give blank results.
The table is set to UTF8 and the field in particular has also been set to UTF8 just in case. I have a charset=utf-8 in the header area of my document.
I made two test posts in my forum, one with straight text and one with a curly quote in it. I then created a simple PHP query to fetch the two rows. The gist of the code is this:
<?php
$con = mysql_connect("XXXXX","XXXXX","XXXXX");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("XXXXX", $con);
echo "<br />";
echo "<br />";
echo "PHP 5.4.9 having issues displaying UniCode characters. MySQL 5.5.28.3. FastCGI has been removed. IIS.";
echo "<br />";
echo "<br />";
$result1 = mysql_query("SET NAMES 'utf8'");
$result = mysql_query("SELECT post_body from ubbt_posts where post_id = 358826");
while($row = mysql_fetch_array($result))
{
echo "Post Body without unicode: " . $row['post_body'];
echo "<br />";
echo "<br />";
}
$result = mysql_query("SELECT post_body from ubbt_posts where post_id = 358825");
while($row = mysql_fetch_array($result))
{
echo "Post Body with unicode: " . $row['post_body'];
echo "<br />";
echo "<br />";
}
mysql_close($con);
?>
The behavior is that the non-unicode post displays properly. The other one doesn't display at all. It's not that it displays a square or block instead of the quote - it doesn't show anything.
I've tried changing the mysql_fetch_array to mysql_fetch_assoc. That didn't help. I tried utf8_encode out of frustration and that didn't help either.
I'd read that some people had issues with the combo of IIS / PHP 5.4.9 / FastCGI so I deinstalled FastCGI. At least I tried my best to - the files are all gone and IIS is pointing again to the php-cgi.exe file. But PHP still reports it's using FastCGI. I think it must be seeing something in the registry? But in any case the files are gone so it really can't be. And it didn't help.
I'll try even wild ideas at this point to get it working. Any thoughts?
UPDATES:
I have now tested this with both PHP and ASP. Both languages give the identical same behavior. My result set with updated code gives these results:
Post Subject without unicode: test post without unicode
Post Body without unicode: test post with just normal characters in it, as well as xyzzy for testing.
Length of body field: 74
Post Subject with unicode: test post with unicode
Post Body with unicode:
Length of body field: 0
So with the length being checked, both ASP and PHP report the length of that field is zero.
Could it be ODBC that is having an issue? Could it be FastCGI still interfering even though I tried to remove it?
UPDATE 2 -
What I had to do to fix this is to reload the data in from a backup, into the table already set as UTF8 and the field set to UTF8. Then I had to do the set names utf8 command in the code right after the connect. I don't think FastCGI was involved here - when I reinstalled FastCGI things were still OK. I still have some niggling issues remaining but I think the table set and reload handled most of my issues.
Lisa
