0

I have php page querying from a SQL database in the following manner:

$virtual_dsn = 'DRIVER={SQL Server};SERVER=myserver;DATABASE=mydatabase';
$connection = odbc_connect($virtual_dsn,user,pass) or die('ODBC Error:: '.odbc_error().' :: '.odbc_errormsg().' :: '.$virtual_dsn);
odbc_exec($connection,mydatabase);

$query = "SELECT * FROM mytable WHERE myfield = 'Séjourné'";
$result = odbc_exec($connection, $query);

I know that there are rows in the table that should match this query but they are not being returned. I have narrowed the problem down to the "é" character; i.e. queries without that character return as expected but I am not able to query records that contain "é". I suspect the problem has something to do with the ecoding of the characters. I hoped there was some way to specify what characterset should be used for my ODBC connection or query. If there is I cannot find it.

What am I missing. Thanks.

mrjrdnthms
  • 1,549
  • 4
  • 24
  • 35

2 Answers2

0

Missing qouta. I rewrite your line.

$query = "SELECT * FROM mytable WHERE myfield = 'Séjourné'";

Crsr
  • 624
  • 3
  • 9
  • That was a typo in the question. – mrjrdnthms Mar 06 '12 at 16:17
  • Ok, then.. you have a chars problem: in console: SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; or.. in query use: COLLATE utf8_bin after where condition, sorry but i don't know what si the best charset for you. – Crsr Mar 06 '12 at 16:50
  • Thanks for your help Crsr but no luck so far. in the sql database the collation for the table is set to SQL_Latin1_General_CP1_CI_AS. I tried: $query = "SELECT * FROM mytable WHERE myfield = 'Séjourné' COLLATE utf8_bin"; and $query = "SELECT * FROM mytable WHERE myfield = 'Séjourné' COLLATE Latin1_General_CP1_CI_AS"; and $query = "SELECT * FROM mytable WHERE myfield = 'Séjourné' COLLATE SQL_Latin1_General_CP1_CI_AS"; None of which worked. – mrjrdnthms Mar 06 '12 at 20:10
  • view my answer.. it was a lot of code to write in a comment :) – Crsr Mar 06 '12 at 20:49
0

Please do this:

create table test1 (
title text)
default charset = utf8, default collate = utf8_general_ci;
insert into test1 values ('tèst');
insert into test1 values ('tèst');
insert into test1 values ('tèst');
insert into test1 values ('tèst');
insert into test1 values ('tèst');
insert into test1 values ('test');
insert into test1 values ('test');
insert into test1 values ('test');
insert into test1 values ('test');

and run this queries:

SELECT * FROM test1 WHERE title = 'tèst'
SELECT * FROM test1 WHERE title = 'test'

What are your results? For me, in both cases, return all of records.

LE:

odbc_exec($connection, "SET NAMES 'UTF8'");
odbc_exec($connection, "SET client_encoding='UTF-8'");

or utf8_general_ci

It is necessary that the results need to be different depending è character?

Crsr
  • 624
  • 3
  • 9