Characters Not Displaying Correctly
Moderator: General Moderators
Characters Not Displaying Correctly
Hi Folks.
I am new to both PHP and MYSQL, and cobbling something together using exmaples that I find onhte web.
What I am trying to do is store the contents of text files into a MYSQL database, then display them via PHP.
The problem I have is that some characters are not tanslated correctly, and shown as either squares or question marks. The original characters are “ and ”
I have tried fiddling around with the mysql collation setting and get slightly different results, but for some reason I can never display the actual original characters.
The PHP I am using is as follows:
Sorry I frogot to mention the code I use, the function is as follows:
function displayStories(){
global $database;
$q = "SELECT username,title,story,viewed,rating,date "
."FROM ".TBL_STORIES." ORDER BY date DESC,username";
$result = $database->query($q);
/* Error occurred, return given name by default */
$num_rows = mysql_numrows($result);
if(!$result || ($num_rows < 0)){
echo "Error displaying info";
return;
}
if($num_rows == 0){
echo "Database table empty";
return;
}
/* Display table contents */
echo "<table align=\"left\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\">\n";
echo "<tr><td><b>Username</b></td><td><b>Level</b></td><td><b>Email</b></td><td><b>Last Active</b></td></tr>\n";
for($i=0; $i<$num_rows; $i++){
$storiesusername = mysql_result($result,$i,"username");
$storiestitle = mysql_result($result,$i,"title");
$storiesstory = mysql_result($result,$i,"story");
$storiesviewed = mysql_result($result,$i,"viewed");
$storiesrating = mysql_result($result,$i,"rating");
$storiesdate = mysql_result($result,$i,"date");
echo "<tr><td>$storiesusername</td></tr>";
echo "<tr><td>$storiestitle</td></tr>";
echo "<tr><td>$storiesstory</td></tr>";
echo "<tr><td>$storiesviewed</td></tr>";
echo "<tr><td>$storiesrating</td></tr>";
echo "<tr><td>$storiesdate</td></tr>";
}
echo "</table><br>\n";sr71_sr71
I have tried using various different collations for MYSQL "Text" field.
Any ideas anyone?
regards.
I am new to both PHP and MYSQL, and cobbling something together using exmaples that I find onhte web.
What I am trying to do is store the contents of text files into a MYSQL database, then display them via PHP.
The problem I have is that some characters are not tanslated correctly, and shown as either squares or question marks. The original characters are “ and ”
I have tried fiddling around with the mysql collation setting and get slightly different results, but for some reason I can never display the actual original characters.
The PHP I am using is as follows:
Sorry I frogot to mention the code I use, the function is as follows:
function displayStories(){
global $database;
$q = "SELECT username,title,story,viewed,rating,date "
."FROM ".TBL_STORIES." ORDER BY date DESC,username";
$result = $database->query($q);
/* Error occurred, return given name by default */
$num_rows = mysql_numrows($result);
if(!$result || ($num_rows < 0)){
echo "Error displaying info";
return;
}
if($num_rows == 0){
echo "Database table empty";
return;
}
/* Display table contents */
echo "<table align=\"left\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\">\n";
echo "<tr><td><b>Username</b></td><td><b>Level</b></td><td><b>Email</b></td><td><b>Last Active</b></td></tr>\n";
for($i=0; $i<$num_rows; $i++){
$storiesusername = mysql_result($result,$i,"username");
$storiestitle = mysql_result($result,$i,"title");
$storiesstory = mysql_result($result,$i,"story");
$storiesviewed = mysql_result($result,$i,"viewed");
$storiesrating = mysql_result($result,$i,"rating");
$storiesdate = mysql_result($result,$i,"date");
echo "<tr><td>$storiesusername</td></tr>";
echo "<tr><td>$storiestitle</td></tr>";
echo "<tr><td>$storiesstory</td></tr>";
echo "<tr><td>$storiesviewed</td></tr>";
echo "<tr><td>$storiesrating</td></tr>";
echo "<tr><td>$storiesdate</td></tr>";
}
echo "</table><br>\n";sr71_sr71
I have tried using various different collations for MYSQL "Text" field.
Any ideas anyone?
regards.
Re: Characters Not Displaying Correctly
Are your text files WORD documents or actually .txt files? First I would try making sure you have an actual .txt file and not just something with a .txt extension. Second I would like to see how you're reading the file into the database.
Re: Characters Not Displaying Correctly
Good question. Originally they are Word docs, but i have tried saving them as text files also via notepad and also wordpad. The way I copy the data into the database is very basic at this stage, and done manually by inserting a record using the phpmyadmin console. When i do the insert the record looks fine in the database, but it appears to be translated via the php code into something else?Jade wrote:Are your text files WORD documents or actually .txt files? First I would try making sure you have an actual .txt file and not just something with a .txt extension. Second I would like to see how you're reading the file into the database.
Re: Characters Not Displaying Correctly
The original word doc shows my test text assr71_sr71 wrote:Good question. Originally they are Word docs, but i have tried saving them as text files also via notepad and also wordpad. The way I copy the data into the database is very basic at this stage, and done manually by inserting a record using the phpmyadmin console. When i do the insert the record looks fine in the database, but it appears to be translated via the php code into something else?Jade wrote:Are your text files WORD documents or actually .txt files? First I would try making sure you have an actual .txt file and not just something with a .txt extension. Second I would like to see how you're reading the file into the database.
“Test Text,” Test Text
an asci notepad file stores this as:
"Test Text," Test Text
but the php code displays it as being:
�Test Text,� Test Text
BUT, i just noticed that if i type into notepad manualy as
"Test Text," Test Text
it then displays fine.
The problem i now have is that many people could be sending in text via various different sources, and i dont want to retype everything, so how can i work out the translation to display correctly?
Re: Characters Not Displaying Correctly
If you are going to store UTF-8 data in MySQL, your tables need to have a
compatible collation as well (it appears that this was not checked in versions prior to
4.1.1).
See http://www.mysql.com/doc/en/Charset-Unicode-sets.html and
http://www.mysql.com/doc/en/Charset-examples.html for more information, where the last
link explains that the database default collation (latin1_swedish_ci) will be used if you
don't specify one for your table.
have fun
compatible collation as well (it appears that this was not checked in versions prior to
4.1.1).
See http://www.mysql.com/doc/en/Charset-Unicode-sets.html and
http://www.mysql.com/doc/en/Charset-examples.html for more information, where the last
link explains that the database default collation (latin1_swedish_ci) will be used if you
don't specify one for your table.
have fun
Re: Characters Not Displaying Correctly
Sorry my testing is moving quickly, and just found something on the word side of things. So i save the word doc from word as a txt file, leaving it set to using "windows (default)" encoding, but enable "allow character substition", everything then seems to work fine.sr71_sr71 wrote:The original word doc shows my test text assr71_sr71 wrote:Good question. Originally they are Word docs, but i have tried saving them as text files also via notepad and also wordpad. The way I copy the data into the database is very basic at this stage, and done manually by inserting a record using the phpmyadmin console. When i do the insert the record looks fine in the database, but it appears to be translated via the php code into something else?Jade wrote:Are your text files WORD documents or actually .txt files? First I would try making sure you have an actual .txt file and not just something with a .txt extension. Second I would like to see how you're reading the file into the database.
“Test Text,” Test Text
an asci notepad file stores this as:
"Test Text," Test Text
but the php code displays it as being:
�Test Text,� Test Text
BUT, i just noticed that if i type into notepad manualy as
"Test Text," Test Text
it then displays fine.
The problem i now have is that many people could be sending in text via various different sources, and i dont want to retype everything, so how can i work out the translation to display correctly?
So as such this could be a workaround, a lengthy one, but possible. But it would be nice to just do a copy and paste 1 time and see the right results.
If you have any ideas how to resolve this, it would be very handy
Re: Characters Not Displaying Correctly
Not quite a good workaround, as now i notice all the formatting has gonesr71_sr71 wrote:Sorry my testing is moving quickly, and just found something on the word side of things. So i save the word doc from word as a txt file, leaving it set to using "windows (default)" encoding, but enable "allow character substition", everything then seems to work fine.
So as such this could be a workaround, a lengthy one, but possible. But it would be nice to just do a copy and paste 1 time and see the right results.
If you have any ideas how to resolve this, it would be very handy
Re: Characters Not Displaying Correctly
Thanks for the links, but i cannot get them to work? They take me back to an Oracle page saying they could not find what i was looking for?idinx wrote:If you are going to store UTF-8 data in MySQL, your tables need to have a
compatible collation as well (it appears that this was not checked in versions prior to
4.1.1).
See http://www.mysql.com/doc/en/Charset-Unicode-sets.html and
http://www.mysql.com/doc/en/Charset-examples.html for more information, where the last
link explains that the database default collation (latin1_swedish_ci) will be used if you
don't specify one for your table.
have fun
Re: Characters Not Displaying Correctly
Making progress now, got the characters to translate properly by doing the following right after making the db connection: mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");sr71_sr71 wrote:Thanks for the links, but i cannot get them to work? They take me back to an Oracle page saying they could not find what i was looking for?idinx wrote:If you are going to store UTF-8 data in MySQL, your tables need to have a
compatible collation as well (it appears that this was not checked in versions prior to
4.1.1).
See http://www.mysql.com/doc/en/Charset-Unicode-sets.html and
http://www.mysql.com/doc/en/Charset-examples.html for more information, where the last
link explains that the database default collation (latin1_swedish_ci) will be used if you
don't specify one for your table.
have fun
The only problem I am trying to resolve now is to retain the line breaks. phpmyadmin reports the line breaks present in the db, but it seems php drops them so i get one line of text???
Re: Characters Not Displaying Correctly
Just so you all know, everything is now working fine.
So just to recap the solutions:
- To fix the unicode problems, i add the following just after the db connection: mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
- To fix the lack of line breaks being returned, I added the use of nl2br to my return results, as follows: $storiesstory = nl2br(mysql_result($result,$i,"story"));
Now I can move on to something else or the next problem
Thanks everyone!
So just to recap the solutions:
- To fix the unicode problems, i add the following just after the db connection: mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
- To fix the lack of line breaks being returned, I added the use of nl2br to my return results, as follows: $storiesstory = nl2br(mysql_result($result,$i,"story"));
Now I can move on to something else or the next problem
Thanks everyone!