I'm using MS SQL Server and CodeIgniter 2 with Active Record for a project I'm working on, and I just stumbled upon this issue:
When I submit a form that contains Chinese or Hindi characters, I store it in a table, and when I view it all I get are question marks. If I try English or Greek characters, everything seems to work fine.
The reason I believe this is something to do with the PHP I'm writing, is because if I copy-paste the chinese text directly in SQL Server Management Studio, all values are stored and displayed perfectly, both on the SQL Studio, and the web application.
These are the db settings I'm using:
$db['local']['dbdriver'] = 'sqlsrv';
$db['local']['dbprefix'] = '';
$db['local']['pconnect'] = FALSE;
$db['local']['db_debug'] = TRUE;
$db['local']['cache_on'] = FALSE;
$db['local']['cachedir'] = '';
$db['local']['char_set'] = 'utf8';
$db['local']['dbcollat'] = 'utf8_general_ci';
$db['local']['swap_pre'] = '';
$db['local']['autoinit'] = TRUE;
$db['local']['stricton'] = FALSE;
This is the structure of the table I'm testing on right now:
CREATE TABLE [dbo].[languages](
[id] [int] IDENTITY(1,1) NOT NULL,
[language] [nvarchar](1024) NULL,
[language_local] [nvarchar](1024) NULL,
[lang_code] [nvarchar](100) NULL,
[core] [bit] NULL,
CONSTRAINT [PK_languages] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
And this is my charset encoding in config.php
$config['charset'] = 'utf-8';
New troubleshooting data
I tried to save the following string through my form: Iñtërnâtiônàlizætiøn
CodeIgniter replied with this error:
An error occurred translating the query string to UTF-16: No mapping for the Unicode character exists in the target multi-byte code page. .
This doesn't appear when I try to store Chinese characters Thank you in advance :)
Looks like this answer is getting a lot of attention, and I feel bad for not posting the actual solution to my problem... I'd guess it's bad etiquette to de-select an answer I selected many years ago so I won't for now. Here goes...
No changes needed to be done to the settings. The problem is query related, and unfortunately CodeIgniter doesn't support the proper query format out of the box.
So when you want to insert multibyte characters into your table, you have to prepend the character N before your string.
So in my example above the query should look like this in order to work
INSERT INTO test_table (title) VALUES (N'Iñtërnâtiônàlizætiøn')
No, CI doesn't currently give you a built in way to do this. It is planed to be added in on CI4, but until then here is a hack for you