Using php and odbc connection, not getting result from SQL with é in query?

Question

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.

If this is your real code, the quoting is invalid: $query = 'SELECT * FROM mytable WHERE myfield = 'Séjourné'; Surround the query string in double quotes. — Michael Berkowski
– Michael Berkowski, Commented Mar 6, 2012 at 0:40
I corrected type in question. That was not the issue. The code works with queries except for ones with "é". — mrjrdnthms
– mrjrdnthms, Commented Mar 6, 2012 at 16:16

Crsr · Accepted Answer · 2012-03-06 06:24:52Z

0

Missing qouta. I rewrite your line.

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

answered Mar 6, 2012 at 6:24

Crsr

6243 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mrjrdnthms Over a year ago

That was a typo in the question.

Crsr Over a year ago

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.

mrjrdnthms Over a year ago

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.

Crsr Over a year ago

view my answer.. it was a lot of code to write in a comment :)

Crsr · Accepted Answer · 2012-03-06 21:21:44Z

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?

Collectives™ on Stack Overflow

Using php and odbc connection, not getting result from SQL with é in query?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related