Foreign Characters with PHP and MySQL

[imperium2335]

How do I convert accented characters etc into their code form so I can put them into my database? I have tried everything I can think of like htmlentities, htmlspecialchars, url_decode etc and nothing seems to work!

 

I am storing content in a table with different fields for different languages, e.g. polish, spanish etc.

I can use the following function:

function getRewriteString($sString) {
     $string = strtolower(htmlentities($sString));
     $string = preg_replace("/&(.)(uml);/", "$1e", $string);
     $string = preg_replace("/&(.)(acute|cedil|circ|ring|tilde|uml);/", "$1", $string);
     $string = preg_replace("/([^a-z0-9]+)/", " ", html_entity_decode($string));
     $string = trim($string, "-");
     return $string;
  }

Which replaces accented chars with their unaccented counterparts but it doesn't work 100% of the time and isn't really what I want because I'd like to have the accented chars as they will be used on different language sites.

 

Thanks in advance.

[Adam]

This works for me:

 

$str = 'éâäà1å';
echo htmlentities($str); // returns: éâäà1å

Did you look at the source?

[imperium2335]

Yes, the source just shows the accented chars, no html entity code .

[imperium2335]

część comes out as is (for polish). ó happens here and there.

[dgoosens]

if you work in Unicode, you will not have to worry about this.

Have a look here:

http://www.richnetapps.com/php-mysql-speak-unicode/

 

Basically,

1. use the mb_ string functions in PHP

2. make sure your DB uses the Unicode charset

3. make sure your HTML has the correct UTF-8 header

 

If you do all of this... you will not have to convert anything

[imperium2335]

Thanks, but it didn't work.

 

The database is now being populated with things like this: S5 część plc

 

this is the source:

OpisS5 część plcadapter kąta prostego 9-polarnychSiemens S7 modułSiemens S7 modułS5 plc 135/155 32 kanałowy moduł wejścia 24VDCSystem 3 wejścia modułuStep7 zawodowych susStandardowych dysków 200vfsa0.37kw-0.75kWSimatic C7-613 kompaktowySiemens dysk najlepsze kartyS7-200 em cyfrowe wejście 221zakresie Simatic pg PIII 1GHz win2000SINAMICS g110 analogowe 200V 1Ph 0.75kWModuł EpromSiemens 6ra płyty analogowe DCS5 plc 135 / 155 16 ch. 24 - 60vdc moduł wejśćSimatic pcs7 v6.0 dokumentacji cdS5 część plcSinumerik 820/850/880 klawiatury panelu operatora

[imperium2335]

Db is set to unicode, i have header('Content-Type: text/html;charset="utf-8"'); at the top on my script, and i have the languages being set to unicode to:

echo $polish    = htmlentities(translate($desc, "pl"), ENT_QUOTES, 'utf-8') ;

[dgoosens]

Db is set to unicode, i have header('Content-Type: text/html;charset="utf-8"'); at the top on my script, and i have the languages being set to unicode to:

echo $polish    = htmlentities(translate($desc, "pl"), ENT_QUOTES, 'utf-8') ;

 

I guess you forgot this then:

$result = mysql_query('SET NAMES utf8');

$result = mysql_query('SET CHARACTER SET utf8');

 

read the whole page !!

[imperium2335]

Hi, now i just get question marks

 

S5 cz??? plc

[dgoosens]

we would really need to know what your code is doing...

could you post the relevant scripts ?

 

thanks,

dGo

[imperium2335]

ok here it is:

<?PHP
header('Content-Type: text/html;charset="utf-8"');

function translate( $text, $destLang, $srcLang = 'en' ) { // Converting to English.

$text = urlencode( $text );
$destLang = urlencode( $destLang );
$srcLang = urlencode( $srcLang );

$trans = @file_get_contents( "http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q={$text}&langpair={$srcLang}|{$destLang}" );
$json = json_decode( $trans, true );

if( $json['responseStatus'] != '200' ) return false; else return $json['responseData']['translatedText'];
}

$columns = 3+1 ;

$file = "C:\Users\TCWH\Desktop\siemens-parts.csv" ;

$fh = fopen($file, 'r') ;

$data = fread($fh, filesize($file)) ;

//$commas = ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,," ;

$rows = explode("\r", $data) ;

include("dbconnect.php") ;

$query = "SELECT EquipmentID FROM parts_table ORDER BY EquipmentID DESC" ;
$result = mysql_query('SET NAMES utf8');
$result = mysql_query('SET CHARACTER SET utf8');
$result = mysql_query($query) ;

$row = mysql_fetch_assoc($result) or die(mysql_error()) ; 

$rowstart = $row['EquipmentID'] + 1 ;

foreach($rows as $current) {

$column = explode(',', $current, 4) ;

$category = $column[0] ;
$partno = $column[1] ;
$desc = ucfirst(strtolower($column[2])) ;

//$french    = htmlentities((utf8_decode(translate($desc, "fr"))), ENT_QUOTES, 'utf-8') ;
//$german    = htmlentities((utf8_decode(translate($desc, "de"))), ENT_QUOTES, 'utf-8') ;
echo $polish    = htmlentities(translate($desc, "pl"), ENT_QUOTES, 'utf-8') ;
//$spanish   = htmlentities(translate($desc, "es"), ENT_QUOTES, 'utf-8') ;
//$italian   = htmlentities((utf8_decode(translate($desc, "it"))), ENT_QUOTES, 'utf-8') ; 
//$arabic  = translate($desc, "ar") ;
//$chinese = translate($desc, "zh") ;
//$port    = translate($desc, "pt") ;
//$cz      = translate($desc, "cz") ;

$query = "INSERT INTO parts_table (EquipmentID, CategoryID, ManufacturerID, PartNumber, DetailURL) VALUES
('$rowstart', '$category', '$manu', '$partno', '$URL')" ;
$result = mysql_query('SET NAMES utf8');
$result = mysql_query('SET CHARACTER SET utf8');
$result = mysql_query($query) or die(mysql_error()) ;

$query = "INSERT INTO languagecontent (ContentID, ContentEn, ContentFr, ContentEs, ContentDe, ContentPl) VALUES ('$rowstart', '$desc', '$french', '$spanish', '$german', '$polish')" ;
$result = mysql_query('SET NAMES utf8');
$result = mysql_query('SET CHARACTER SET utf8');
mysql_query($query) or die(mysql_error()) ;

if($column[0] == "" or $column[0] == NULL) break ;

$rowstart++ ;

}
//print_r($french) ; echo "<br />" ;
//print_r($german) ; echo "<br />" ;
//print_r($polish) ; echo "<br />" ;
//print_r($spanish) ; echo "<br />" ;
//print_r($italian) ; echo "<br />" ;
//print_r($arabic) ; echo "<br />" ;

?>

[imperium2335]

Anyone know how to do it?

[dgoosens]

hi,

 

I notice you are getting the data from a CSV file...

You should make sure that it is Unicode encoded as well...

 

Pay attention, if you are working with Excel...

Choose the correct file format.

[imperium2335]

Hi, it gets only the english content from there which is then sent to googles translation api. So the encoding on the csv does not matter.

[dgoosens]

Ok...

So I did a little research and came across this site:

http://verens.com/2006/05/05/some-utf8-problems-and-solutions/

 

Where it says:

A lot of further work revealed that, in fact, the default mysql_* functions simply cannot handle character sets very well. Instead, you must use the mysqli_* functions. There is simply no simple way around this!

 

Could you give it a try with the mysqli functions ?