Wrting umlauts to a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Wrting umlauts to a database

Post by Rupo »

Hi Folks,

I've really some hard problems writing some umlauts as 'ä ü Ö' to my mysql-db. Therefore I've some little script

Code: Select all

header('Content-type: text/html; charset=utf-8');
define('host','localhost');
define('user','root');
define('pass','');
define('name','ajax');

$db=mysql_connect(host, user, pass);
mysql_select_db(name, $db) or die(mysql_error());

$inhalt='äöü öäü'
$sql="Insert INTO comments (com_text) VALUES ('".$inhalt."')";
mysql_query($sql, $db);
echo $sql;
But the result is frustrating:
äöü öäü
So what should be the right collation(s) for my database

Thanks for some advice
so long
Rupo
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Probably UTF-8.

On a side note, it's inadvisable to use constants to define your database log in credentials. Why? Because you can't destroy them. If someone is able to inject some code into yours it's very simple to find out that information.
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Post by Rupo »

feyd wrote: On a side note, it's inadvisable to use constants to define your database log in credentials. Why? Because you can't destroy them. If someone is able to inject some code into yours it's very simple to find out that information.
sure, but's only a script to test the waters! Normely, I put them in protected vars.

Back to the topic. Looking at the script I sent the query by utf-8 and the db-insert looks like I postet above. But id should look like 'üäö a.s.o.'

so long
Rupo
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The database doesn't know that your page is encoded in UTF-8, nor does it care. You have to tell it the data is UTF-8.
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Post by Rupo »

well there's a final question:

the umlauts are still saved in the db like 'öüö' if sening utf-8. Is this wright:

by using the sniplet

Code: Select all

header('Content-type: text/html; charset=utf-8');
$db=mysql_connect(host, user, pass);
mysql_select_db(name, $db) or die(mysql_error());
$str='öüö';
$sql="Insert INTO comments (com_header) VALUES ('".$str."')";
mysql_query($sql, $db);
so long
rupo
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The database has no knowledge of header(), so no.
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Post by Rupo »

so whats going wrong????
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Please try

Code: Select all

$db=mysql_connect(host, user, pass);
mysql_select_db(name, $db) or die(mysql_error());

$query = 'SELECT Length(com_header), com_header FROM comments';
$result = mysql_query($query, $db) or die(mysql_error());

while( $row=mysql_fetch_array($result, MYSQL_NUM) ) {
  echo "{$row[0]} : '{$row[1]}'<br />\n";
}
and post the output
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Post by Rupo »

6 : 'öüö'
6 : 'öüö'
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Looks like your database is not aware of the utf-8 encoding but the browser is.
The browser "sees" three two-byte characters while the mysql "sees" 6 one-byte characters.
Take a look at http://dev.mysql.com/doc/refman/5.1/en/ ... yntax.html
Rupo
Forum Commoner
Posts: 25
Joined: Thu Jul 05, 2007 11:22 am

Post by Rupo »

Thanks ... I've already looked at this earlier.

Now I#ve added something

Code: Select all

$db=mysql_connect(host, user, pass);
mysql_select_db(name, $db) or die(mysql_error());
$sql = "SET NAMES utf8";
mysql_query($sql,$db);

$query = "insert into comments (com_writer) Values ('ööö')";
$result = mysql_query($query, $db) or die(mysql_error());
Is this a correct way to solve my problem?

so long
Rupo
Post Reply