special character in php pdo sqlsrv not handled correctly

Question

In my database I have an entry CARIÑO when I select it in database I get CARIÃO showing when I inspect element in browser. When I try to save it again in database I get error saying:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xC2\x91O' for column I want to know how to fix this error in a way that it will show CARIÑO in browser and be saved again as CARIÑO into database.

In my database connection I have:

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'mysql:charset=utf8mb4'); 
//Establishes the connection
$this->_connection = new PDO("sqlsrv:Server=IP;Server=$serverName,1433; Database=".DB_NAME."",DB_USER,DB_PASS,$options);

I used sample in stackoverflow.com/questions/279170/utf-8-all-the-way-through for using the mysql:charset=utf8mb4

And in my select I have:

while ($selected_row = $stmt->fetch(PDO::FETCH_ASSOC)) {
     // $selected_row['column'] = $column;
     //echo $selected_row['patlast'];//this ouputs CARIÃO
     $rows1[] = array_map('utf8_encode', $selected_row);
     $rows[] =  array_map('utf8_encode', $selected_row);
}

The database I select from is ms sql server.

UPDATE:

After running from here:

mb_detect_encoding($selected_row['patlast'], mb_detect_order(), true) === 'UTF-8' ?  $content= $selected_row['patlast'] :  $content= mb_convert_encoding($selected_row['patlast'], 'UTF-8');
echo "patlast: ".$content; // i get patlast: CARIÃ‘O
echo "mb_detect_encoding: ". mb_detect_encoding($selected_row['patlast'], mb_detect_order(), true);//i get mb_detect_encoding: UTF-8

@YourCommonSense what is ms sql equivalent for `mysql:charset=utf8mb4` is there an equivalent? — Martin, Nov 03 '17 at 08:46
i dont know, i don't work with sqlsrv. but I am sure there should be existing answers for sqlsrv somewhere — Your Common Sense, Nov 03 '17 at 08:56
https://stackoverflow.com/questions/16800915/php-pdo-charset-utf8-an-invalid-keyword-charset-was-specified-in-the-dsn-str — Your Common Sense, Nov 03 '17 at 08:58
but what is strange, the error message you get looks like it's from mysql, not sql server — Your Common Sense, Nov 03 '17 at 08:59
i saw that casting will work like `cast (patlast as nvarchar) as patlast` but it didnt. i am selecting from ms sql database @YourCommonSense — Martin, Nov 03 '17 at 09:06
what i dont understand is when i select directly in microsoft sql server management studio i get the correct the value which is `CARIÑO` in system using php pdo i dont get the correct @YourCommonSense — Martin, Nov 03 '17 at 09:17

score 0 · Accepted Answer · answered Nov 06 '17 at 05:20

0

Changed :

$rows1[] = array_map('utf8_encode', $selected_row);
$rows[] =  array_map('utf8_encode', $selected_row);

To

$rows1[] = array_map(null, $selected_row);
$rows[] =  array_map(null, $selected_row);

Now gets the correct name CARIÑO

answered Nov 06 '17 at 05:20

Martin

365
4
7
22

special character in php pdo sqlsrv not handled correctly

1 Answers1