CODEWITHSUNDEEP
phpdatabasefileencodingutf-8
Gary Willoughby
Gary Willoughby

Reputation: 52608

Encoding issue when reading values from a utf-8 file in PHP and putting them into a database.`

I'm reading a UTF-8 encoded file using PHP and splatting the contents directly into a database. The problem is that when i encounter a character such as ” , it places the following †into the database.

How can i encode this correctly, i'm reading a UTF-8 file and my database column's collation is a UTF-8. What am i doing wrong? Is there a nice function i'm missing? Any help is welcome.

This is my table:

CREATE TABLE tblProductData (
  intProductDataId int(10) unsigned NOT NULL AUTO_INCREMENT,
  strProductName varchar(50) NOT NULL,
  strProductDesc varchar(255) NOT NULL,
  strProductCode varchar(10) NOT NULL,
  dtmAdded datetime DEFAULT NULL,
  dtmDiscontinued datetime DEFAULT NULL,
  stmTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (intProductDataId),
  UNIQUE KEY (strProductCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

EDIT:

I'm reading the date like this:

$hFile = @fopen($FileName, "r") or exit("\nUnable to open file: " . $FileName);
if($hFile)
{
    while(!feof($hFile))
    {
        $Line = fgets($hFile);
        $this->Products[] = new Product($Line);
    }
    fclose($hFile);
}

Upvotes: 1

Views: 937

Answers (2)

genesis
genesis

Reputation: 50982

use 

mysql_query("SET NAMES utf8");

just after connection to DB and be sure that browser encoding is in utf-8, too

 header("Content-Type: text/html; charset: utf-8");

Upvotes: 1

Fabio
Fabio

Reputation: 19216

You should set your connection encoding with this query

SET NAMES 'utf8'

before storing any data.

Keep also in mind that some database gui or web gui (i.e. phpMyAdmin) shows wrong encoding even if your data are encoded correctly. This happen for example with SequelPro on Mac and with phpMyAdmin in some environments.

You should trust your browser, i.e. show your inserted content in a page which has the

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

header and see if the data are shown correctly. Or even better trust mysql command line using the shell:

echo 'SELECT yourdata FROM your table' | mysql -uuser -pyourpwd db_name

Upvotes: 0

Related Questions