0

I have an sql database with sql_latin1_general_cp1_ci_as collation.
the database will contain about 3 language (English,Turkish,Arabic).

At the main page I set the charset to utf-8 as:

header('Content-Type: text/html; charset=UTF-8');
<meta charset="utf-8">

And I use this code in my php page:

$sql_server = "server";
$sql_user = "user";
$sql_password = "password";
$sql_database = "database";
$sql_conn_string = 'Driver={SQL Server};Server='.$sql_server.';Database='.$sql_database.';';
if ($sql_conn = odbc_connect($sql_conn_string, $sql_user, $sql_password)){
    echo 'Connected';
    $name = $_POST['cust_name'];
    if (odbc_exec($sql_conn,"INSERT INTO inv01 (cust) VALUES (N'".$name."')")){
        echo 'is inserted';
    }
    else{
        echo 'is not inserted';
    }
    odbc_close($sql_conn);
}
else{
    echo 'Connection Error';
}

When I enter an English words there is no problem
But when I use the other languages the words is inserted as unclear characters

I tried to use iconv but it's not working

$name = iconv("UTF_8","Windows-1252",$name);
//and
$name = iconv("UTF_8","UCS-2LE",$name);

What is the best collation in my case if sql_latin1_general_cp1_ci_as is not, and is there another solution without change the collation

I hope that my question is not repeated because I have read many questions here and I have not found a correct answer.

Thanks.

Rober.Ya
  • 65
  • 3
  • 12

1 Answers1

3

You can try with this:

  • Ensure that your 'cust' column is nvarchar().

  • Encode your html and php files in UTF-8 (I usually use Notepad++ for this step).

  • Convert input data.

Code (based on your example):

<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge"/>
<meta charset="utf-8">
<?php
    ...
    $name = $_POST['cust_name'];
    $name = iconv('UTF-8', 'UTF-16LE', $name);
    $name = bin2hex($name);
    $sql  = "INSERT INTO inv01 (cust) VALUES (CONVERT(nvarchar(MAX), 0x".$value."))";
    ...
?>
</head>
<body></body>
</html>
Zhorov
  • 28,486
  • 6
  • 27
  • 52