Solved

Swedish characters in php/MySQL site

Posted on 2010-11-15
6
631 Views
Last Modified: 2013-12-12
Hi there

How can I store and display swedish characters e.g. the o with the dots on it and the a with the arrows etc. in a mysql database, and make it render properly in a browser via PHP?

Also would like to be able to edit database fields via HTML textfield and textarea controls and have the information populate correctly back into the database.

Any guidance would be gratefully received!

Many thanks
0
Comment
Question by:butterhook
6 Comments
 
LVL 13

Accepted Solution

by:
dsmile earned 100 total points
ID: 34135334
All you need is to create insert/update & display pages with same charset (I recommend utf-8).
If you do so, I guarantee 100% that swedish characters can be stored and retrieved as normal as english characters :)

I attach sample code that proves what I'm saying.

This is test schema

CREATE TABLE test_sw (
  id int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  KEY id (id)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
 <head>
  <title> Test Swedish page </title>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8" />

 </head>

 <body>
<?php
$host = 'localhost'; $db_user = 'root'; $db_password = '';
$database = 'test';
$connect = mysql_connect ( $host, $db_user, $db_password );
mysql_select_db ( $database );

if (isset($_POST['submit'])) {
	$result = mysql_query("INSERT INTO test_sw (id, name) VALUES ('', '".$_POST['name']."')");
	if (!$result) {
	   echo 'Could not run query: ' . mysql_error();
	   exit;
	}
}

$result = mysql_query('SELECT id, name FROM test_sw');
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}

while ($row = mysql_fetch_array($result)) {
   echo 'string #'. $row['id'] .': '. $row['name'] .'<br />';
}

?>
<form method="post" action="?">
	<input type="text" name="name" size="50" maxlength="255"></input> <input type="submit" name="submit" value="Insert">
</form>
</body>
</html>

Open in new window

0
 
LVL 4

Assisted Solution

by:max-hb
max-hb earned 100 total points
ID: 34135874
Hi!
You probably want to set your client-server connection encoding to utf8 as well:
Insert the following at line 14 of the above script.

mysql_query('SET NAMES utf8');
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 34138068
Here is a script you can install and test with.  No mention of UTF-8 anywhere.  In fact, if activate line 40, the special characters will come back out as questionmarks.
<?php // RAY_accented_chars.php

error_reporting(E_ALL);





// IMPORTANT PAGES FROM THE MANUALS

// MAN PAGE: http://php.net/manual/en/ref.mysql.php

// MAN PAGE: http://php.net/manual/en/mysql.installation.php

// MAN PAGE: http://php.net/manual/en/function.mysql-error.php





// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE

$db_host = "localhost"; // PROBABLY THIS IS OK

$db_name = "??";        // GET THESE FROM YOUR DBA / HOSTING PROVIDER

$db_user = "??";

$db_word = "??";





// OPEN A CONNECTION TO THE DATA BASE SERVER

// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php

if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))

{

    $errmsg = mysql_errno() . ' ' . mysql_error();

    echo "<br/>NO DB CONNECTION: ";

    echo "<br/> $errmsg <br/>";

}



// SELECT THE MYSQL DATA BASE

// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php

if (!$db_sel = mysql_select_db($db_name, $dbcx))

{

    $errmsg = mysql_errno() . ' ' . mysql_error();

    echo "<br/>NO DB SELECTION: ";

    echo "<br/> $errmsg <br/>";

    die('NO DATA BASE');

}

// IF WE GOT THIS FAR WE CAN DO QUERIES





// DO NOT MOVE TO UTF-8

// if (!mysql_query('SET NAMES utf8')) die(mysql_error());





// CREATE A TABLE TO TEST WITH

$sql = "CREATE TEMPORARY TABLE my_table (

        _key INT         NOT NULL AUTO_INCREMENT,

        name VARCHAR(32) NOT NULL DEFAULT '',

        PRIMARY KEY(_key)

        ) ";

$res = mysql_query($sql);



// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS

if (!$res)

{

    $errmsg = mysql_errno() . ' ' . mysql_error();

    echo "<br/>QUERY FAIL: ";

    echo "<br/>$sql <br/>";

    die($errmsg);

}







// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES

// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php

$names[] = mysql_real_escape_string("O'Réilly");

$names[] = mysql_real_escape_string("ENCYCLOPÆDIA");

$names[] = mysql_real_escape_string("Umlöut");

$names[] = mysql_real_escape_string("€uro");





foreach ($names as $name)

{

    // MAKING AN INSERT QUERY, USING THE ESCAPED STRING AND TESTING THE RESULTS

    $sql = "INSERT INTO my_table (name) VALUES ('$name')";

    $res = mysql_query($sql);



    // IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS

    if (!$res)

    {

        $errmsg = mysql_errno() . ' ' . mysql_error();

        echo "<br/>QUERY FAIL: ";

        echo "<br/>$sql <br/>";

        die($errmsg);

    }

    // IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS



    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED

    // MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php

    $_key  = mysql_insert_id();



    echo "<br/>KEY: $_key AND NAME WITH ESCAPE STRING INCLUDED: $name ";

}





// MAKING A SELECT QUERY AND TESTING THE RESULTS

$sql = "SELECT * FROM my_table";

$res = mysql_query($sql);



// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS

if (!$res)

{

    $errmsg = mysql_errno() . ' ' . mysql_error();

    echo "<br/>QUERY FAIL: ";

    echo "<br/>$sql <br/>";

    die($errmsg);

}

// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS





// ITERATE OVER THE RESULTS SET

echo "<pre>";

while ($row = mysql_fetch_assoc($res))

{

    var_dump($row);

}

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 34138127
This part, however, may present a bit of a challenge: "...edit database fields via HTML textfield and textarea controls..."

There are no keys for some of those things on my keyboard.  I used a clip library with copy/paste to create the script above.  When I typed the Euro symbol directly into the browser string, it got urlencoded into a numeric entity.  These produce equivalent outputs

http://www.laprbass.com/RAY_bounce_get.php?q=€
http://www.laprbass.com/RAY_bounce_get.php?q=%80

Using this code below, I was able to paste the Euro and other things into the text box.  It came through fine.

HTH, ~Ray
<?php // RAY_post_example.php

error_reporting(E_ALL);

echo "<pre>\n"; // MAKE IT EASY TO READ







// THIS ILLUSTRATES THE RELATIONSHIP BETWEEN THE 'ACTION' AND THE 'FORM' WHEN COMBINED INTO A SINGLE PHP SCRIPT FILE







// SET A DEFAULT VALUES FOR A VARIABLE TO PREPOPULATE THE FORM - THIS COULD COME FROM A DATA BASE, OR MIGHT BE EMPTY

$my_INPUT_Field = 'ORIGINAL DATA';



// IF THE FORM WAS FILLED IN, COPY THE INPUT INTO OUR VARIABLE

if (!empty($_POST["my_INPUT_Field"]))

{

    $my_INPUT_Field = $_POST["my_INPUT_Field"];

}





// HAS ANYTHING BEEN POSTED - IF SO, $_POST IS SET AND CONTAINS THE DATA

if (!empty($_POST))

{

    // SHOW THE POST ARRAY

    var_dump($_POST);

}

// THIS IS THE END OF THE ACTION SCRIPT









// THIS IS THE FORM SCRIPT - FOR CONVENIENCE WE DROP OUT OF PHP INTO HTML

?>

<form method="post">

TYPE SOMETHING HERE:

<input type="text"   name="my_INPUT_Field"   value="<?php echo $my_INPUT_Field; ?>" />

<input type="submit" name="my_SUBMIT_Button" value="go" />

</form>

Open in new window

0
 
LVL 17

Assisted Solution

by:shinuq
shinuq earned 100 total points
ID: 34215845
You need to place this in your script for the utf 8 to work as you are expecting.

hope this helps

$mysql_link = mysql_connect($host, $username, $password);
mysql_select_db($database, $mysql_link); 
mysql_query(‘SET character_set_results=utf8');
mysql_query(‘SET names=utf8');
mysql_query(‘SET character_set_client=utf8');
mysql_query(‘SET character_set_connection=utf8');
mysql_query(‘SET character_set_results=utf8');
mysql_query(‘SET collation_connection=utf8_general_ci’);

Open in new window

0
 
LVL 1

Author Closing Comment

by:butterhook
ID: 34481837
Great work fellers
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now