Hi There;
I have a MS-SQL 2005 in my computer. There is a person table, and this person table have three columns; name, surname and address.They are nvarchar(50). I hold the data of my company on it.
User can make some queries over it via HTML. PHP code does the following:

1- get the data from user via PHP, with $_POST
2- prepare a SQL query sentence such like that: SELECT * FROM person WHERE name = '$_POST';
3- connect to database;
4- send the query
5- And print the result.

The problem is that database contains Turkish names, as a consequence of it Turkish characters such as "Güray" or "Bahadır". When user chooses these Turkish names, database returns no result despite there are many records with these names.
Moreover when user chooses names that contains fully English characters, such as "Ali", the Turkish characters can not be printed properly.

Any help is greatly appreciated.

Technical details:

Product: Microsoft SQL Server Express Edition
OS: Microsoft Windows NT 5.1 (2600)
Platform: NT INTEL X86
Version: 9.00.1399.06
Language: English (USA)
Memory: 3199 (MB)
Server Collation: Turkish_CI_AS
Is Clustered: False

Server side language: PHP
Server: IIS
Browser: IE 8

This code prepares SQL issue:

/*
     * EN :Establishing connection to database. This point will need update in deployment.
     */ 
    $virtual_dsn = 'DRIVER={SQL Server};SERVER=REHAB-EB07315EE\SQLEXPRESS;DATABASE=osman';
    $connection = odbc_connect($virtual_dsn,'sa','123456') or 
        die('ODBC Error:: '.odbc_error().' :: '.odbc_errormsg().' :: '.$virtual_dsn);
    
    /*
     * EN : Connecting to the database.
     */
    odbc_exec($connection,'osman');
    
    //# For diagnostical purposes
    echo $SQLSentence;

     /*
     * EN : Query is sending.
     */
    $result = odbc_exec($connection , $SQLSentence);

    if(!$result)
    {
        echo "Query failed " .odbc_error();
    }
    
//This line prints the result in table format. Parameters are the features of the table.
    odbc_result_all_ex($result,'Border=1 cellspacing=1 cellpadding=5', "style='FONT-FAMILY:Tahoma; FONT-SIZE:9pt; BORDER-BOTTOM:solid 1pt gree'");

    sqlsrv_close($connection);

This code prints the result: (Encoding change done in underscore)

function odbc_result_all_ex($res, $sTable, $sRow) 
    { 
        $cFields = odbc_num_fields($res); 

        $strTable = "<table $sTable>"; 
        $strTable .= "<tr>"; 
         
        for ($n=1; $n<=$cFields; $n++) 
        { 

            $strTable .= "<td $sRow><b>". str_replace("_", " ", odbc_field_name($res, $n)) . "</b></td>"; 

        } 
        $strTable .= "</tr>"; 

        $temp; 
        while(odbc_fetch_row($res)) 
        { 
            $strTable .= "<tr>"; 
                for ($n=1; $n<=$cFields; $n++) 
                { 
                    if (odbc_result($res, $n)=='') 
                    { 
                        $strTable .= "<td $sRow>&nbsp;</td>"; 
                    } 
                    else 
                    { 
                     [U] $temp =mb_convert_encoding(odbc_result($res, $n), "UTF-8");[/U] 
                        $strTable .= "<td $sRow>" . $temp . "</td>"; 
                    } 

                } 
            $strTable .= "</tr>"; 
        } 

        $strTable .= "</table>"; 

        echo $strTable; 

    }

Since this is an the MSSQL thread, I guess the first most obvious question is, have you run the query (with appropriate Turkish characters) in SSMS stand-alone? I built a little table with nvarchar(50) columns, populated it with some common Turkish phrases and did some selects, and it worked fine.

Here's what I built:

CREATE TABLE [dbo].[person](
	[name] [nvarchar](50) collate TURKISH_CI_AS NULL,
	[surname] [nvarchar](50) collate TURKISH_CI_AS NULL ,
	[address] [nvarchar](50) collate TURKISH_CI_AS NULL 
)

Here's the data I used:

INSERT INTO [osman].[dbo].[person] ([name], [surname] ,[address])
     VALUES ('Çok teşekkürler!', 'Türkçe biliyor musunuz?', 'Görüşürüz')
INSERT INTO [osman].[dbo].[person] ([name], [surname] ,[address])
     VALUES ('Türkçe biliyor musunuz?', 'Görüşürüz', 'Çok teşekkürler!')
INSERT INTO [osman].[dbo].[person] ([name], [surname] ,[address])
     VALUES ('Görüşürüz', 'Çok teşekkürler!', 'Türkçe biliyor musunuz?')
INSERT INTO [osman].[dbo].[person] ([name], [surname] ,[address])
     VALUES ('Joe Smith', 'Smith', '123 Smith Street')
GO

Here are the queries I ran:

select * from person 
where name like '%Çok%'

select * from person 
where name like '%e%'

select * from person
where name = 'Çok teşekkürler!'

select * from person
where name = 'Joe Smith'

I also checked the unicode values for the characters, and they all seem to be less than 255 (so the UTF-8 character set should have them all).

Maybe this is a codepage issue on the target computer? Perhaps this question should be asked in the PHP section?

There is no problem in SSMS. The database work with no problem. These problems were arisen from the web side.
I've asked, but nobody has answered. The situation drives me mad. I will implement the following.
ğ=1
ü=2
ş=3
ç=4
ö=5
ı=6
İ=7

Since therefore, "Güray" turn out to be "G2ray" and "Bahadır" will be "Bahad6r".I will turn out to normal in PHP side.

I've understood that the strangest and the most unique problems are often connected not with the larger but with the smaller issues.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.