Charset problems driving me insane.

Question

FreddieBambino 2 Light Poster

14 Years Ago

Ok, I have been searching everywhere for solutions for charset problems I have.
I have php website and backend Mysql database. In some fields I need to put in letters from the Greek alphabet.
If I use a form to send info to the database and type in Greek letters, and then extract it from the database and splash on the screen in the browser, then it looks fine, the greek letters show up all correctly. But if I go and take a look at the database for example in phpmyadmin, where the greek letters are supposed to be, then it just returns some jibberish. with question marks, arrows pointing up and stuff like that. This is a problem since I need to be able to export the database to an excel file, and there it returns the same jibberish as in the database.

Im going to show you some of the variables in phpmyadmin and an example of how I would create the database and tables with charsets and collation.
The database i would make somehow like this:

CREATE DATABASE `database1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Table I would make like this:
create table table1(
id_number int(5) primary key auto_increment,
amount int(7) NOT NULL,
owner int(1) NOT NULL,
category char(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
description varchar(70) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
date date NOT NULL
)type=innodb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Here I make collate latin1_swedish_ci collate on some columns just to save space(since this will only include letters like A, B and so on).

variables in phpmyadmin:
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir C:\xampp\mysql\share\charsets\
collation connection utf8_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

When I go to php, connect to the database and check the connection it says latin1.
For example:
$db = new mysqli('localhost','user','password','database1');
$charset = $db->character_set_name();
echo " Charset of connection is: ".$charset." ";

this would echo latin1.
In the header of the html file I have UTF-8.
I also tried to change some apache charset settings for example in iconv and so on. but nothing worked. I also put in a php header at the top of the document with something like this header('Content-Type: text/html; charset=utf-8'). I also included UTF-8 inside the form tags.

What do I need to do here, this is driving me crazy. Please help.

mysql

2 Contributors
10 Replies
263 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by colweb

colweb 13 Posting Whiz

14 Years Ago

I have done some tests. If your mysql table charset is set to Greek, you can enter Greek characters in the table and when viewed with the query browser, the do look like Greek and not garbage. So instead of

CREATE DATABASE `database1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use

CREATE DATABASE `database1` DEFAULT CHARACTER SET greek COLLATE greek_general_ci;

If you would export this table to excel, the Greek characters appear in the excel worksheet.

But... now you have a new problem. If you query this table and want the result in a web page, you get garbage. To solve that, use the correct charset "ISO-8859-7" and use htmlentities. For example:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html" charset="ISO=8859-7">
</head>
<body>
<?php
  // your code to query the database
  echo "<p>".htmlentities($field)."</p>"; // should display the correct characters.
?>

Finally, I'm not a Greek, nor does I understand anything of that language. I did however copied some Greek text from Greek web sites and used it to test if the strange characters went in the database correctly and came out again.

colweb 13 Posting Whiz

14 Years Ago

Did some more testing. The following code will display Greek in a web browser and in the zip file you can see, Greek in mysql, in Excel and in Firefox. The thing is that your table needs to be in charset greek and you tell php to communicate in greek as well. No htmlentities needed that way.

<?php
// html header;
echo "<html><head></head><body>";

// connect to database
$db = new mysqli('localhost','root','localhost','daniweb');
if (mysqli_connect_error()) {
	echo "<p>Can't connect with database: ".mysqli_connect_error()."</p>";
	exit();
}
else { // if connected set charset to greek
	if (!$db->set_charset("greek")) {
		echo "<p>Error: ".$db->error."</p>";
		exit();
	}
}

// prepare and execute the query
$query = "SELECT * FROM test";
$result = $db->query($query);
if ($db->errno) {
	echo "<p>Error: ".$db->errno."  ".$db->error."</p>";
	exit();
}
$num_results = $result->num_rows;

// loop through the results
for ($i=0; $i < $num_results; $i++) {
	$row = $result->fetch_assoc();
	echo "<p>".$row['id']." : ".$row['thetext']."</p>";
}

echo "</body></html>";
?>

By the way, have no idea what the Greek means, did copy it from a Greek website.

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

greek.zip (242.54 KB)

Edited 14 Years Ago by colweb because: n/a

colweb 13 Posting Whiz

14 Years Ago

Really don't know what could be wrong on your site. If I ask the character set in use, I get Latin (see the image). But it doesn't matter for the Greek in this database. The table is set to Greek and I get Greek out of it.

When I do it from within the php file I get this output:

Array ( [0] => character_set_client [Variable_name] => character_set_client [1] => greek [Value] => greek )

After adding these lines to the code posted earlier:

}

$query = "show variables like 'character%'";
$result = $db->query($query);
$row = $result->fetch_array();
print_r ($row);

// prepare and execute the query

Edited 14 Years Ago by colweb because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

FreddieBambino 2 Light Poster · Answer 1 · 2010-05-07T08:57:36+00:00

I have done some tests. If your mysql table charset is set to Greek, you can enter Greek characters in the table and when viewed with the query browser, the do look like Greek and not garbage. So instead of
CREATE DATABASE `database1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use
CREATE DATABASE `database1` DEFAULT CHARACTER SET greek COLLATE greek_general_ci;
If you would export this table to excel, the Greek characters appear in the excel worksheet.
But... now you have a new problem. If you query this table and want the result in a web page, you get garbage. To solve that, use the correct charset "ISO-8859-7" and use htmlentities. For example:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html" charset="ISO=8859-7">
</head>
<body>
<?php
 // your code to query the database
 echo "".htmlentities($field).""; // should display the correct characters.
?>
Finally, I'm not a Greek, nor does I understand anything of that language. I did however copied some Greek text from Greek web sites and used it to test if the strange characters went in the database correctly and came out again.

Hi mate. Thanks for the reply.

I tried what you said, but unfortunately it doesnt work. It even makes it worse. Since the content in the database(the content I added to the database after I changed the database and script like you suggested too) is still jibberish, and the content in the browser is now also jibberish.
Now my variables in phpmyadmin are:
character set client utf8
(Global value) latin1
character set connection greek
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir C:\xampp\mysql\share\charsets\
collation connection greek_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

It still says characters set server latin1, char set system UTF-8, collation database and server is still latin1_swedish_ci.

If what you suggested works where you are, but doesnt work for me, then it must be these settings that are wrong for me or something.

FreddieBambino 2 Light Poster · Answer 2 · 2010-05-07T17:02:52+00:00

Did some more testing. The following code will display Greek in a web browser and in the zip file you can see, Greek in mysql, in Excel and in Firefox. The thing is that your table needs to be in charset greek and you tell php to communicate in greek as well. No htmlentities needed that way.
<?php
// html header;
echo "<html><head></head><body>";

// connect to database
$db = new mysqli('localhost','root','localhost','daniweb');
if (mysqli_connect_error()) {
	echo "Can't connect with database: ".mysqli_connect_error()."";
	exit();
}
else { // if connected set charset to greek
	if (!$db->set_charset("greek")) {
		echo "Error: ".$db->error."";
		exit();
	}
}

// prepare and execute the query
$query = "SELECT * FROM test";
$result = $db->query($query);
if ($db->errno) {
	echo "Error: ".$db->errno." ".$db->error."";
	exit();
}
$num_results = $result->num_rows;

// loop through the results
for ($i=0; $i < $num_results; $i++) {
	$row = $result->fetch_assoc();
	echo "".$row['id']." : ".$row['thetext']."";
}

echo "</body></html>";
?>
By the way, have no idea what the Greek means, did copy it from a Greek website.

Yes I see mate, but unfortunately it doesnt work from where I am sitting. Im thinking its a problem with the server or database variables.
Does anyone know how to change those variables that I displayed earlier.

FreddieBambino 2 Light Poster · Answer 3 · 2010-05-07T18:05:16+00:00

Really don't know what could be wrong on your site. If I ask the character set in use, I get Latin (see the image). But it doesn't matter for the Greek in this database. The table is set to Greek and I get Greek out of it.
When I do it from within the php file I get this output:
Array ( [0] => character_set_client [Variable_name] => character_set_client [1] => greek [Value] => greek )
After adding these lines to the code posted earlier:
}

$query = "show variables like 'character%'";
$result = $db->query($query);
$row = $result->fetch_array();
print_r ($row);

// prepare and execute the query

Yea i get the same. I extracted some stuff from one table and the western alphabet name came out alright, but not the greek ones. If I use utf_general_ci though, it displays both greek and western alphabet correctly in the browser, the only problem is, that it stores the Greek alphabet as jibberish in the database.

Example of extracted data in browser with greek_general_ci
Array ( [0] => character_set_client [Variable_name] => character_set_client [1] => greek [Value] => greek )

Michael Jackson

?????�?????????????????????�??

?????????�?�???�????

Vigdis Finnbogardottir

Olafur Grimsson

?????????�?�???�????

?????�??????????????????

????

?�????????

colweb 13 Posting Whiz · Answer 4 · 2010-05-07T19:12:44+00:00

Did you change the charset of the table to Greek? And if so, in php use $db->set_charset("greek") before you insert new data or extract the new inserted data.
Data that is already in the table won't come out good. Changing the charset setting on a table doesn't alter the data already in it.

So, change it to greek, in you php file set the charset also to greek, insert new data and try to select and display that. By me it did work. (see the previous examples).

FreddieBambino 2 Light Poster · Answer 5 · 2010-05-07T19:32:07+00:00

Did you change the charset of the table to Greek? And if so, in php use $db->set_charset("greek") before you insert new data or extract the new inserted data.
Data that is already in the table won't come out good. Changing the charset setting on a table doesn't alter the data already in it.
So, change it to greek, in you php file set the charset also to greek, insert new data and try to select and display that. By me it did work. (see the previous examples).

Yes I changed the database to greek, that table and that column that I withdrew data from. Then I inserted new data and it still didnt work.
I will try this: $db->set_charset("greek") before I add some new data as well.
Thanks for your effort mate, it is really appreciated.

FreddieBambino 2 Light Poster · Answer 6 · 2010-05-11T03:51:09+00:00

Yes I changed the database to greek, that table and that column that I withdrew data from. Then I inserted new data and it still didnt work.
I will try this: $db->set_charset("greek") before I add some new data as well.
Thanks for your effort mate, it is really appreciated.

Yes, it isnt still working. :confused:

I also tried to save the files in the greek iso format. Tried to insert new data, but still didnt work.

I am thinking if it could perhaps be the html header.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html" charset="ISO=8859-7">
<meta name="description" content="Cyprus House.">
<meta name="keywords" content="HTML, tags, commands">
<title>Welcome</title>
</head>

For example. The !Doctype tag there, does it ruin for me if it is set to Transitional//EN?

Again, thanks for your patience, I really appreciate it.

colweb 13 Posting Whiz · Answer 7 · 2010-05-11T13:53:56+00:00

Have looked at my code again and did see that the browser is using utf-8 character-encoding. So the start of your html page should look like this:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
         "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8"> 
.... <rest of the code> ....

The mysql table is set to Greek and very important, you tell your php script to communicate with the mysql server in Greek as well by using:

$db = new mysqli('localhost','root','localhost','daniweb');
$db->set_charset("greek")

before doing an insert or select. To query other tables from the same database that contain other languages, use $db->set_charset again but with the other language of course, before doing any inserts or selects.

In the declaration of the form you also might need a charset definition. Like so:

<form method=post action="nextpage.php" enctype="multipart/form-data" accept-charset="ISO-8859-7">

This way your form will accept Greek characters and handle them the right way.

Finally, ardav posted an interesting piece of code for when you have to use multiple languages on the same page (see here: http://www.daniweb.com/forums/thread282576.html post number 5)
The code ardav posted:

<div dir="rtl" lang="ar-sa" xml:lang="ar-sa"><p>مرحبا العالم</p></div>
<div dir="ltr" lang="en-gb" xml:lang="en-gb"><p>Hello World</p></div>
<div dir="ltr" lang="hi" xml:lang="hi"><p>नमस्ते दुनिया</p></div>