2

I have a table which contains name of teachers. Some names are like René Visser having special characters. When I write the SQL query for displaying the name, the special characters are replaced by � symbols. I have tried cast() but it's not working properly. My query is like this.

$qry = mssql_query("SELECT CAST(FirstName_1 AS NVARCHAR(250)) AS Name FROM 
    tbl_teachers");

The FirstName_1 column is nvarchar type. I have tried to cast FirstName_1 to VARBINARY(8000), then casting result to IMAGE like following.

CAST(CAST(FirstName_1  AS VARBINARY(8000)) AS IMAGE) AS Name.
shA.t
  • 16,580
  • 5
  • 54
  • 111
Techy
  • 2,626
  • 7
  • 41
  • 88

3 Answers3

3

You should have UTF-8 encoding for the SQL server.

Then, make sure you send the encoding headers also from php using :

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

Cosmin
  • 1,482
  • 12
  • 26
  • Or set the page encoding to Windows-1252. SQL Server's default. – John Bell Sep 07 '15 at 12:45
  • what response header for charset do you recieve bro @Techy ? – Cosmin Sep 07 '15 at 12:53
  • How to know the response header?I am not familiar with this.@Cosmin – Techy Sep 07 '15 at 12:54
  • 1
    Agreed With @Cosmin. Make sure the page is setting the charset to UTF-8 and ensure that the DB tables are also set to UTF-8. One more thing to ensure is that the font you are displaying in has the characters you need (99% will with things like é)... easiest way is to set to Arial or Helvetica to test. – Kevin Nagurski Sep 07 '15 at 12:58
  • 1
    @Techy If you have firefox, download firebug plugin. If you have chrome, you already have a console. Press F12, refresh page, look in the network tab, click on the main request and then in the right tab click Headers, scroll and search for Content type in response headers... Or google it if you need more details – Cosmin Sep 07 '15 at 13:00
  • @Cosminthe content type is text/html; charset=utf-8 – Techy Sep 07 '15 at 13:08
  • 1
    Then I suggest you try @Kevin Nagurski 's answer form comments and be sure the font has those characters. That can be a problem too! – Cosmin Sep 07 '15 at 13:09
  • 1
    One more thing, it could be that the characters were mangled going into the DB... try a manual update: `UPDATE tbl_teachers SET FirstName_1 = "René" WHERE id = *WHATEVER*` When it's all set to UTF-8, you shouldn't need to do any casting. – Kevin Nagurski Sep 07 '15 at 13:14
1

You will need to specify the charset, or if you have already, set it to Windows-1252. It's likely your page is reading in the data with UTF-8 encoding. Which explains the ? symbols.

<head>
    <meta charset="Windows-1252">
</head>
John Bell
  • 2,350
  • 1
  • 14
  • 23
1

You most likely have a charset issue. Your query has little to do with this, you don't need to cast it.

You'll need to set the charset of the connection, the PHP and HTML header and the document itself as the same charset. UTF-8 will most likely cover all of the special characters you'll ever need.

Below is some things you could do.

  • ini_set('mssql.charset', 'UTF-8'); (Have this run upon connecting to your database)
  • Set both PHP and HTML header to UTF-8
    • PHP: header('Content-Type: text/html; charset=utf-8'); (has to be called before any and all output)
    • HTML: <meta charset="utf-8"> (has to be inside the <head>-tag
  • Save the document in UTF-8 encoding. If you're doing it in Notepad++, it's Format -> Convert to UFT-8 (you may also choose UTF-8 w/o BOM)

  • The database itself, and it's tables, may need to be set to UTF-8. This can be done with the query below (need only to be run once):

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Keep in mind that all parts of your application has to be set to the same type of charset, or you'll experience those kind of things in your database.

Qirel
  • 25,449
  • 7
  • 45
  • 62