[RESOLVED] PHP, ADODB and Unicode

nzseries1

Hi everyone,

Can someone please tell me how come my unicode is getting replaced by question marks? In my database I have three unicode first names, and one name "John". As you can see, my encoding is correct as the first two chinese characters print correctly (well they do on my display). Here is the output (the code is displayed below):

TEST - &#27827;&#21335;
First Name: ??
First Name: ???
First Name: ??
First Name: John

CODE IS AS FOLLOWS:

$db = getcwd().'\..\..\testdb.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db");
$query = "SELECT firstname FROM Trainers";
$result = $conn->execute($query);

print("TEST - &#27827;&#21335;"."<BR><BR>");

while (!$result->EOF) {
  print("First Name: ".$result->Fields["firstname"]->value."<BR>");
  $result->MoveNext();
}

Thanks very much in advance,
Matthew

Piranha

Is the encoding in the database correct?

nzseries1

Hi Piranha,

Thanks for your reply. When I display the table in Microsoft Access, it shows correctly within Access itself.

Also, if I run print($result->Fields["firstname"]->type), I get a value of 202 (adVarWChar), which I believe is correct.

Regards,
Matthew

Piranha

I have no idea how Access handles this kind of things. I would suggest that you download and use MySQL instead, it is a much better database and it is free.

Roger_Ramjet

It is, as you suspect, the code page - but not the code page used in display, it is the code page used on the COM connection.

In the manual it tells you that you have to specify the connection encoding in either php_ini directives or as parameters to the com object you create; otherwise straight ansi for the platform is the default. (also depends on php ver 4 or 5)

Now if you can do it in php_ini then do so, otherwise add the code page parameter to your com object call

$conn = new COM('ADODB.Connection',,CP_UTF8); 
// or, I believe if my memory serves me
$conn = new COM('ADODB.Connection',,65001);

May have to tinker with that a bit

Roger_Ramjet

PS Piranha, Access and MySQL exist in different realms and serve different functions. It is true that mysql is much better for web apps, but access is much better for local desktop and small client/server apps in a lan because of the integrated tools it provides. Usually when you see someone trying to connect to access from php it is to some pre-existing lan app that contains data they need.

nzseries1

Thanks very much for all your advice, I will try modifying the COM connection later when I get a chance (I don't have access to php.ini).

Roger you are right, Access is something that pre-exists on the network I am connecting to. However, if I can't get this to work my next step may be to convert the database to MySQL!

Roger_Ramjet

As an interim fix you will find it very much easier to connect from access to mysql using odbc and a dsn connection rather than going in the opposite direction. You can then just link to the mysql tables in access and write access queries to upload the data to a mysql backend and display from there using php. This is one area where MS scores over php/mysql.

Migrating the whole thing to an mysql backend is a whole different proposition as it will probably entail major re-writes of your access forms and modules because of the different data types and inbuilt sql functions. Very much depends on the complexity and size of the access app of course.

Roger_Ramjet

PS why php, why not MS data access pages? Again, the tools come complete with access 2003, or use new free VS2005 Express.

nzseries1

PHP is what I know, basically. Plus, the application was already there, I just have to modify it to allow unicode names.
I'm not a database or ODBC or ADO expert unfortunately either!

nzseries1

Thanks so much Roger. I got there with this:
$conn = new COM('ADODB.Connection',NULL, CP_UTF8);
Thanks again!!!
Matt