save Ø in mysql table and not Ã˜

Question

My table look like this:

CREATE TABLE `friends` 
(
    `id` bigint(20) NOT NULL default '0',
    `fb_id` bigint(20) NOT NULL default '0',
    `name` varchar(100) NOT NULL default '',

) TYPE=MyISAM charset=utf8;

I add to the db like this:

            $sql = "insert ignore into `friends` (`id`, `fb_id`, `name`)" .
                            "values (:id, :fb_id, :name)";
                try 
                {
                    $stmt = $this->db->prepare($sql); 
                    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
                    $stmt->bindParam(':fb_id', $fb_id, PDO::PARAM_INT);
                    $stmt->bindParam(':name', $name, PDO::PARAM_STR);

                    $result = $stmt->execute();
                    $stmt->closeCursor(); 
                }       
                catch (Exception $e)
                {
                die ($e->getMessage() ); 
                }

How can I saveØystein and not Ã˜ystein. I have set the charset to utf8 not sure what more I can do.

EDIT:

Upgraded my db_connect function:

        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME;
        $driver_options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8' );

        try
        {
            $this->db = new PDO($dsn, DB_USER, DB_PASS, $driver_options);
        }

Still got the same problem..

Please share your final solution with us, since it might help others. — SteAp, May 04 '11 at 22:25

score 0 · Answer 1 · answered Apr 23 '11 at 02:48

0

Try adding $this->db->exec('SET CHARACTER SET utf8') after connecting to the DB.

answered Apr 23 '11 at 02:48

morgar

2,339
17
16

Do you mean `$result = $stmt->execute('SET CHARACTER SET utf8');`, it did not work.. – ganjan Apr 23 '11 at 15:09
@ganjan try `SET NAMES utf8` as well – Pekka Apr 23 '11 at 15:10
Nope, after connecting to the db: $this->db = new PDO($dsn, DB_USER, DB_PASS, $driver_options); $this->db->exec('SET CHARACTER SET utf8'); It's working for me. – morgar Apr 23 '11 at 15:41

score 0 · Answer 2 · answered Apr 23 '11 at 02:50

0

Did you tell the browser which text encoding to use on the page, which submits the form and the page, which later on shows the result retrieved from the database?

In the header of your HTML page should appear this:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

answered Apr 23 '11 at 02:50

SteAp

11,853
10
53
88

I am using this : ` ` does not seem to make a difference. – ganjan Apr 23 '11 at 15:19

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

You might also want to try running SET NAMES utf8: How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework

edited May 23 '17 at 10:33

Community

1
1

answered Apr 23 '11 at 03:38

PureForm

1,003
8
15

I updated my `db_connect` function as you can see, but that did not help. I think i am using `SET NAMES` correctly.. – ganjan Apr 23 '11 at 15:17
Hmm, I doubt it will make a difference, but could you try making the 'UTF8' lowercase? – PureForm Apr 24 '11 at 21:57
Moreover, you might want to try explicitly sending the charset header... take a look here: https://pureform.wordpress.com/2008/03/23/make-your-website-completely-utf-8-friendly/ – PureForm Apr 24 '11 at 21:59

score 0 · Answer 4 · edited Mar 07 '13 at 16:05

Have you tried adding the charset to the DSN like this:

$dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ';charset=UTF-8';

Could you run this test code to track down the problem a step further?

<?php

echo '<html>'
   . '<head>'
   . '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>'  
   . '</head>'
   . '<body>'
   ;

error_reporting(E_ALL);
ini_set("display_errors", 1);

define( 'DB_HOST', 'localhost' );
define( 'DB_NAME', 'test' );
define( 'DB_USER', 'root' );
define( 'DB_PASS', 'root' );

$dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ';charset=UTF-8';

$db = new PDO($dsn, DB_USER, DB_PASS ); 

$id = 12;
$fb_id = '23';

if ( ! isset( $_POST[ 'name'] )) {

    echo '<form method="POST" >Strange letters go here <input type="text" name="name"></form>'
       . '</body>' 
       ;
    exit;
}

$name = $_POST[ 'name'];


$sql = "insert ignore into `friends` (`id`, `fb_id`, `name`)" .
                            "values (:id, :fb_id, :name)";
try
{
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':fb_id', $fb_id, PDO::PARAM_INT);
    $stmt->bindParam(':name', $name, PDO::PARAM_STR);

    $result = $stmt->execute();
    $stmt->closeCursor();

    echo 'OK';
}
catch (Exception $e)
{
    die ($e->getMessage() );
}

$sql = 'SELECT *'
     . 'FROM `friends` '
     ;

try
{
    $stmt = $db->prepare($sql);

    $result = $stmt->execute();

    while( $result = $stmt->fetch(PDO::FETCH_ASSOC) ) {

        echo '<br />' . print_r( $result );

    }

    $stmt->closeCursor();

    echo 'OK';
}
catch (Exception $e)
{
    die ($e->getMessage() );
}


echo '<body>';

If I submit this

öäüÖÄÜß

the page reads and emits back this

öäüÖÄÜß

while I see this in phpMyAdmin (likely an bug in phpMyAdmin):

Ã¶Ã¤Ã¼Ã–Ã„ÃœÃŸ

save Ø in mysql table and not Ã˜

4 Answers4