Saturday, January 31, 2009

Insert utf-8 unicode data with PHP and MS SQL Server

The n- columns offer the best solution. I would give ADODB another
shot. COM is problematic in PHP4. Support might have improved in PHP5.
In theory, if you pass CP_UTF8 as codepage to COM(), you'd get UTF-8
text out of the resultsets. I remember that when I tried it though,
nothing happened. This was a couple years ago. Maybe newer versions of
ADODB would work better.

If you're using PHP on a Windows platform you can use the PHP COM
extension to communicate with SQL Server via ADO. The PHP COM extension
is capable of translating UTF-8 to UCS-2 and back if you specify so as
the third parameter:

$oDb = new COM('ADODB.Connection', NULL, CP_UTF8);

This way you can use Unicode UTF-8 within PHP and Unicode UCS-2 within
SQL Server with all the translations done for you automatically.

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<form action="p.php" method="POST">
password<input type="text" name="password">

note<input type="text" name="note">

<input type="hidden" name="submist_info" value="yes">
<input type="submit">
$myServer = "localhost";
$myUser = "";
$myPass = "";
$myDB = "";

//create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection", NULL, CP_UTF8)
or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr); //Open the connection to the database

//declare the SQL statement that will query the database
$query = "SELECT TOP 1 * FROM my_member ORDER by ID desc";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
echo $num_columns . " Fields

for ($i=0; $i < $num_columns; $i++) { $fld[$i] = $rs->Fields($i);

echo "";

while (!$rs->EOF) //carry on looping through while there are records
echo "";
for ($i=0; $i < $num_columns; $i++) { echo "";
echo "";
$rs->MoveNext(); //move on to the next record

echo "
" . $fld[$i]->value . "

if( $_POST['submist_info'] == "yes" ){
$password = $_POST['password'];
$note = $_POST['note'];

//$password = mb_convert_encoding($password,"UCS2","UTF-8");
//$note = mb_convert_encoding($note,"UCS2","UTF-8");

// Note: the N before the value data. N stands for National Character for inserting unicode utf-8 to SQL server
$query = "INSERT INTO cwn_member ( name, password, note2 ) VALUES ( 'dan".$i."', N'".$password."', N'".$note."' ) ";


//close the connection and recordset objects freeing up resources


$rs = null;
$conn = null;


on top of the script:


before querying data:

SET NAMES 'utf8';

No comments: