Microsoft SQL Server (MSSQL) Collation and Unicode Support

Note: This KB only applies to phpGrid Enterprise and Universal users who have the full source code access.

Database Type:

First of all, use "odbc_mssql" or generic "odbc" as database type (DB_TYPE) in conf.php using your database is SQL Server. 

Unicode:

Secondly check if the target column types are nvarchar (or nchar) to store Unicode string data.

Collation:

Current ADOdb driver has limited MSSQL support for non-latin characters collation such as Japanese, Croatian, or Chinese etc. If your SQL Server database collation is not the default SQL_Latin1_General_CP1_C, the string contains any special Unicode characters (e.g. german), like: üäöß, may be stored as gibberish such as üäöß.  If so, you may need to create PHP characters mapping function to decode data during view (data.php) and encode data during save (edit.php).  

Example:

Below is the example to create character mapping for Croatian collation (e.g. Croatian_CI_AI). 

In data.php create characters mapping array and function for decoding:

$cp1252_map = array( 
"\xc2\x80" => "\xe2\x82\xac", /* EURO SIGN */ 
"\xc2\x82" => "\xe2\x80\x9a", /* SINGLE LOW-9 QUOTATION MARK */ 
"\xc2\x83" => "\xc6\x92", /* LATIN SMALL LETTER F WITH HOOK */ 
"\xc2\x84" => "\xe2\x80\x9e", /* DOUBLE LOW-9 QUOTATION MARK */ 
"\xc2\x85" => "\xe2\x80\xa6", /* HORIZONTAL ELLIPSIS */ 
"\xc2\x86" => "\xe2\x80\xa0", /* DAGGER */ 
"\xc2\x87" => "\xe2\x80\xa1", /* DOUBLE DAGGER */ 
"\xc2\x88" => "\xcb\x86", /* MODIFIER LETTER CIRCUMFLEX ACCENT */ 
"\xc2\x89" => "\xe2\x80\xb0", /* PER MILLE SIGN */ 
"\xc2\x8a" => "\xc5\xa0", /* LATIN CAPITAL LETTER S WITH CARON */ 
"\xc2\x8b" => "\xe2\x80\xb9", /* SINGLE LEFT-POINTING ANGLE 
QUOTATION */ 
"\xc2\x8c" => "\xc5\x92", /* LATIN CAPITAL LIGATURE OE */ 
"\xc2\x8e" => "\xc5\xbd", /* LATIN CAPITAL LETTER Z WITH CARON */ 
"\xc2\x91" => "\xe2\x80\x98", /* LEFT SINGLE QUOTATION MARK */ 
"\xc2\x92" => "\xe2\x80\x99", /* RIGHT SINGLE QUOTATION MARK */ 
"\xc2\x93" => "\xe2\x80\x9c", /* LEFT DOUBLE QUOTATION MARK */ 
"\xc2\x94" => "\xe2\x80\x9d", /* RIGHT DOUBLE QUOTATION MARK */ 
"\xc2\x95" => "\xe2\x80\xa2", /* BULLET */ 
"\xc2\x96" => "\xe2\x80\x93", /* EN DASH */ 
"\xc2\x97" => "\xe2\x80\x94", /* EM DASH */

"\xc2\x98" => "\xcb\x9c", /* SMALL TILDE */ 
"\xc2\x99" => "\xe2\x84\xa2", /* TRADE MARK SIGN */ 
"\xc2\x9a" => "\xc5\xa1", /* LATIN SMALL LETTER S WITH CARON */ 
"\xc2\x9b" => "\xe2\x80\xba", /* SINGLE RIGHT-POINTING ANGLE 
QUOTATION*/ 
"\xc2\x9c" => "\xc5\x93", /* LATIN SMALL LIGATURE OE */ 
"\xc2\x9e" => "\xc5\xbe", /* LATIN SMALL LETTER Z WITH CARON */ 
"\xc2\x9f" => "\xc5\xb8" , /* LATIN CAPITAL LETTER Y WITH 
DIAERESIS*/

"\xc3\x88" => "\xc4\x8c", /* Croatian letters */ 
"\xc3\xa8" => "\xc4\x8d", /* Croatian letters */ 
"\xc3\x86" => "\xc4\x86", /* Croatian letters */ 
"\xc3\xa6" => "\xc4\x87", /* Croatian letters */ 
//"\xd0" => "\xc4\x90", /* Croatian letters */ 
"\xc3\xb0" => "\xc4\x91", /* Croatian letters */ 
);

function cp1252_to_utf8($str) { 
  global $cp1252_map; 
  return strtr(utf8_encode($str), $cp1252_map); 
}
After line:
$col_name = $db->field_name($result, $j); 
change to
$data[] = cp1252_to_utf8($row[$col_name]);



In edit.php create characters mapping array and function for encoding:

function utf8_to_cp1252($string) '
{
static $transform = array( 
"\xE2\x82\xAC" => "\x80", 
"\xE2\x80\x9A" => "\x82", 
"\xC6\x92" => "\x83", 
"\xE2\x80\x9E" => "\x84", 
"\xE2\x80\xA6" => "\x85", 
"\xE2\x80\xA0" => "\x86", 
"\xE2\x80\xA1" => "\x87", 
"\xCB\x86" => "\x88", 
"\xE2\x80\xB0" => "\x89", 
"\xC5\xA0" => "\x8A", 
"\xE2\x80\xB9" => "\x8B", 
"\xC5\x92" => "\x8C", 
"\xC5\xBD" => "\x8E", 
"\xE2\x80\x98" => "\x91", 
"\xE2\x80\x99" => "\x92", 
"\xE2\x80\x9C" => "\x93", 
"\xE2\x80\x9D" => "\x94", 
"\xE2\x80\xA2" => "\x95", 
"\xE2\x80\x93" => "\x96", 
"\xE2\x80\x94" => "\x97", 
"\xCB\x9C" => "\x98", 
"\xE2\x84\xA2" => "\x99", 
"\xC5\xA1" => "\x9A", 
"\xE2\x80\xBA" => "\x9B", 
"\xC5\x93" => "\x9C", 
"\xC5\xBE" => "\x9E", 
"\xC5\xB8" => "\x9F", 
"\xC2\xA0" => "\xA0", 
"\xC2\xA1" => "\xA1", 
"\xC2\xA2" => "\xA2", 
"\xC2\xA3" => "\xA3", 
"\xC2\xA4" => "\xA4", 
"\xC2\xA5" => "\xA5", 
"\xC2\xA6" => "\xA6", 
"\xC2\xA7" => "\xA7", 
"\xC2\xA8" => "\xA8", 
"\xC2\xA9" => "\xA9", 
"\xC2\xAA" => "\xAA", 
"\xC2\xAB" => "\xAB", 
"\xC2\xAC" => "\xAC", 
"\xC2\xAD" => "\xAD", 
"\xC2\xAE" => "\xAE", 
"\xC2\xAF" => "\xAF", 
"\xC2\xB0" => "\xB0", 
"\xC2\xB1" => "\xB1", 
"\xC2\xB2" => "\xB2", 
"\xC2\xB3" => "\xB3", 
"\xC2\xB4" => "\xB4", 
"\xC2\xB5" => "\xB5", 
"\xC2\xB6" => "\xB6", 
"\xC2\xB7" => "\xB7", 
"\xC2\xB8" => "\xB8", 
"\xC2\xB9" => "\xB9", 
"\xC2\xBA" => "\xBA", 
"\xC2\xBB" => "\xBB", 
"\xC2\xBC" => "\xBC", 
"\xC2\xBD" => "\xBD", 
"\xC2\xBE" => "\xBE", 
"\xC2\xBF" => "\xBF", 
"\xC3\x80" => "\xC0", 
"\xC3\x81" => "\xC1", 
"\xC3\x82" => "\xC2", 
"\xC3\x83" => "\xC3", 
"\xC3\x84" => "\xC4", 
"\xC3\x85" => "\xC5", 
"\xC3\x86" => "\xC6", 
"\xC3\x87" => "\xC7", 
"\xC3\x88" => "\xC8", 
"\xC3\x89" => "\xC9", 
"\xC3\x8A" => "\xCA", 
"\xC3\x8B" => "\xCB", 
"\xC3\x8C" => "\xCC", 
"\xC3\x8D" => "\xCD", 
"\xC3\x8E" => "\xCE", 
"\xC3\x8F" => "\xCF", 
"\xC3\x90" => "\xD0", 
"\xC3\x91" => "\xD1", 
"\xC3\x92" => "\xD2", 
"\xC3\x93" => "\xD3", 
"\xC3\x94" => "\xD4", 
"\xC3\x95" => "\xD5", 
"\xC3\x96" => "\xD6", 
"\xC3\x97" => "\xD7", 
"\xC3\x98" => "\xD8", 
"\xC3\x99" => "\xD9", 
"\xC3\x9A" => "\xDA", 
"\xC3\x9B" => "\xDB", 
"\xC3\x9C" => "\xDC", 
"\xC3\x9D" => "\xDD", 
"\xC3\x9E" => "\xDE", 
"\xC3\x9F" => "\xDF", 
"\xC3\xA0" => "\xE0", 
"\xC3\xA1" => "\xE1", 
"\xC3\xA2" => "\xE2", 
"\xC3\xA3" => "\xE3", 
"\xC3\xA4" => "\xE4", 
"\xC3\xA5" => "\xE5", 
"\xC3\xA6" => "\xE6", 
"\xC3\xA7" => "\xE7", 
"\xC3\xA8" => "\xE8", 
"\xC3\xA9" => "\xE9", 
"\xC3\xAA" => "\xEA", 
"\xC3\xAB" => "\xEB", 
"\xC3\xAC" => "\xEC", 
"\xC3\xAD" => "\xED", 
"\xC3\xAE" => "\xEE", 
"\xC3\xAF" => "\xEF", 
"\xC3\xB0" => "\xF0", 
"\xC3\xB1" => "\xF1", 
"\xC3\xB2" => "\xF2", 
"\xC3\xB3" => "\xF3", 
"\xC3\xB4" => "\xF4", 
"\xC3\xB5" => "\xF5", 
"\xC3\xB6" => "\xF6", 
"\xC3\xB7" => "\xF7", 
"\xC3\xB8" => "\xF8", 
"\xC3\xB9" => "\xF9", 
"\xC3\xBA" => "\xFA", 
"\xC3\xBB" => "\xFB", 
"\xC3\xBC" => "\xFC", 
"\xC3\xBD" => "\xFD", 
"\xC3\xBE" => "\xFE", 
"\xC3\xBF" => "\xFF", 
"\xc4\x90" => "\xd0", 
"\xc4\x91" => "\xF0", 
"\xc4\x8c" => "\xC8", 
"\xc4\x8d" => "\xE8", 
"\xc4\x86" => "\xC6", 
"\xc4\x87" => "\xE6"

); 
return strtr($string, $transform); 
}
Then change line :
$arrFields[$key] =$value;
to
$arrFields[$key] = utf8_to_cp1252($value);

Datagrid Display

  1. Cannot display datagrid in IE8, but works in Firefox
  2. Display Icon/Image In a Virtual Column
  3. Change date display format
  4. Collapse Datagrid on Initial Display
  5. Microsoft SQL Server (MSSQL) Collation and Unicode Support
  6. Data is not showing up (blank grid) in MS SQL, MySql, or other database
  7. Display datagrid somewhere else on the page
  8. Foreign characters display
  9. How to wrap text a cell?
  10. I have 3 different status (1,2 and 3) - I want to assign values to each.. active, inactive and archived .. how do I do this? If I set conditional statement , only the latest value works
  11. I want checkbox columns to display as checkboxes, not 1's and 0's.
  12. Refresh without post
  13. set_locale not working
  14. Customize Edit Form Layout
  15. How to sort phpGrid Data on two columns
  16. Column Order Position
  17. How to "crop" text so that the text won't wrap
  18. How to format text capitalization and other formats
  19. Always Display Integrated Search Toolbar When Page Load
  20. Can Virtual Column Be Used to Create New Column for Database Table?
  21. How to change the height of a row (not header row but data row)?
  22. Replacing Hyperlinks with Images
  23. Fix group sum floating (decimal) issue
  24. Resize Grid According to Current Screen Size
  25. Format UNIX Timestamp to Human Readable Time Format Using Custom Formatter
  26. Change Master Detail Grid Layout e.g. side by side
  27. Remove Duplicate Values in Group Summary
  28. Set Row Height
  29. Display a value in a cell that references a value in another table
  30. Mergy Multiple Columns in A Single Column
  31. Column Color
  32. Set the grid width to the parent DIV
  33. Make Change to phpGrid Default CSS stylesheet
  34. grid width issue
  35. PHPGrid to go to a specific page during load
  36. Example not working
  37. I just want to be able to adjust hours and minutes.
  38. Data bar solid color?
  39. Conditional format - compare two cells
  40. Escape HTML characters in datagrid
  41. Elongated grid when theme is set
  42. How to display total sum of a column of ALL the records in the footer?
  43. Set datagrid to the width of its parent DIV
  44. Display group summary without any detail records
  45. Remove horizontal scroll when set_autowidth() is set to true
  46. Displaying Header vertically

Feedback and Knowledge Base