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('[spanphonebook]', $conn);

$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_AS NULL ,
      [BOOK] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DES] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ITEM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ORD] [int] NULL ,
      [PHONE] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) 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.
LVL 1
mrtwiceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rafranciscoCommented:
First, try changing your DES column to NVARCHAR and see if it makes any difference.
geotigerCommented:

Check the language setting on the server where you run your PHP script.
mrtwiceAuthor Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rafranciscoCommented:
>> Is there a way for me to see exactly what the SQL server recieved as the insert SQL statement?  <<

You can use the SQL Server Profiler to monitor what SQL Server received.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrtwiceAuthor Commented:
>> 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?
rafranciscoCommented:
>> 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.
rafranciscoCommented:
I believe the question has been answered by identifying where the problem is.
mrtwiceAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.