mrtwice
asked on
Character encoding is wrong when data is inserted from PHP script
When I use the following sql in query analyzer:
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s Café', '0001', '1', '')
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')
I get:
DES
---------------------
Churro's Café
Hablamos Español
When I use the following PHP script:
$conn = mssql_connect();
mssql_select_db('[spanphon
$sql1 = "INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s Café', '0001', '1', '')";
$sql2 = "INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')";
mssql_query($sql1, $conn );
mssql_query($sql2, $conn);
I get this:
DES
---------------------
Churro's CafT
Hablamos Espa±ol
My table was created like this:
CREATE TABLE [XDEFINETEST] (
[AA_ACCT] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_
[BOOK] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_
[DES] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_
[ITEM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_
[ORD] [int] NULL ,
[PHONE] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
I have tested my php script with a Sqlite database and a MySql database and the character encoding seems to work just fine with them, so I don't think it is a PHP problem. That leaves me with a MSSQL problem.
Quick answers are appreciated if they work, but I am inclined to award points to the person who sticks with me until the problem is solved. Thanks.
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s Café', '0001', '1', '')
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')
I get:
DES
---------------------
Churro's Café
Hablamos Español
When I use the following PHP script:
$conn = mssql_connect();
mssql_select_db('[spanphon
$sql1 = "INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s Café', '0001', '1', '')";
$sql2 = "INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')";
mssql_query($sql1, $conn );
mssql_query($sql2, $conn);
I get this:
DES
---------------------
Churro's CafT
Hablamos Espa±ol
My table was created like this:
CREATE TABLE [XDEFINETEST] (
[AA_ACCT] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_
[BOOK] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_
[DES] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_
[ITEM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_
[ORD] [int] NULL ,
[PHONE] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
I have tested my php script with a Sqlite database and a MySql database and the character encoding seems to work just fine with them, so I don't think it is a PHP problem. That leaves me with a MSSQL problem.
Quick answers are appreciated if they work, but I am inclined to award points to the person who sticks with me until the problem is solved. Thanks.
First, try changing your DES column to NVARCHAR and see if it makes any difference.
Check the language setting on the server where you run your PHP script.
ASKER
rafrancisco:
I should have mentioned that I tried that with no change in the result.
geotiger:
What language setting? PHP, SQL, Windows???
Like I said though, I don't think it is a PHP problem.
Is there a way for me to see exactly what the SQL server recieved as the insert SQL statement? The transaction log should have this information, but I don't know of a way to translate the transaction log into sql. It is possible that the mssql extension in PHP is causing the problem, but I wouldn't be able to tell that unless I could see exactly what the SQL server was recieving.
I should have mentioned that I tried that with no change in the result.
geotiger:
What language setting? PHP, SQL, Windows???
Like I said though, I don't think it is a PHP problem.
Is there a way for me to see exactly what the SQL server recieved as the insert SQL statement? The transaction log should have this information, but I don't know of a way to translate the transaction log into sql. It is possible that the mssql extension in PHP is causing the problem, but I wouldn't be able to tell that unless I could see exactly what the SQL server was recieving.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> You can use the SQL Server Profiler to monitor what SQL Server received. <<
Excellent, here is what it showed me when I highlighted all the rows in the monitor. The first insert is from Query Analyzer and the second two are from PHP. Note that in the second two the characters are wrong:
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')
go
exec sp_server_info 18
go
use [spanphonebook]
go
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s CafT', '0001', '1', '')
go
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Espa±ol', '0004', '4', '')
go
I am not sure how profiler works. Do these results confirm that the problem is in PHP and not in the SQL server?
Excellent, here is what it showed me when I highlighted all the rows in the monitor. The first insert is from Query Analyzer and the second two are from PHP. Note that in the second two the characters are wrong:
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Español', '0004', '4', '')
go
exec sp_server_info 18
go
use [spanphonebook]
go
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('504-885-6516-0000', 'LA05', 'Churro''s CafT', '0001', '1', '')
go
INSERT INTO XDEFINETEST (AA_ACCT, BOOK, DES, ITEM, ORD, PHONE) VALUES ('502-223-3200-0000', 'KY05', 'Hablamos Espa±ol', '0004', '4', '')
go
I am not sure how profiler works. Do these results confirm that the problem is in PHP and not in the SQL server?
>> Do these results confirm that the problem is in PHP and not in the SQL server? <<
Yes, this confirms that the problem is in PHP and not in SQL Server.
Yes, this confirms that the problem is in PHP and not in SQL Server.
I believe the question has been answered by identifying where the problem is.
ASKER
My appologies for the delay, I forgot that I still needed to take care of this.
Turns out it actually had something to do with Apache. I was running PHP 5.2 and Apache 1.3x. I decided to upgrade PHP, but before I did that I upgraded to the latest Apache 2. When I went back, the problem was solved. Pretty wierd.
However, profile monitor is a great tool, I have used it several times since this thread. Sorry for being slow to award the points. My bad.
Turns out it actually had something to do with Apache. I was running PHP 5.2 and Apache 1.3x. I decided to upgrade PHP, but before I did that I upgraded to the latest Apache 2. When I went back, the problem was solved. Pretty wierd.
However, profile monitor is a great tool, I have used it several times since this thread. Sorry for being slow to award the points. My bad.