5

HI, I have a problem with this query

SELECT * FROM table WHERE `name` LIKE '%tést%'

the HMTL and the SQL table both have utf-8 encoding but unfortunately there is no mysql_set_charset('utf-8') and I'm NOT able to use it.

I've tried with iconv("UTF-8", "ISO-8859-1", $name) but the query matches only test.

I want to match all of these : test, tést, tèst, tëst (as it would work with mysql_set_charset )

edit:

SET NAMES utf8 is nigher possible ... the database is with utf8 encoding, unfortunately the content is being filled from web without mysql_set_charset nor SET NAMES.

Currently if these functions are used the results are messed up.

version() 5.1.41-3ubuntu12.9

edit2:

when I use SET NAMES utf8 it matches only tést and they look like tést

when I use iconv("UTF-8", "ISO-8859-1", $name) it matches only test

Teneff
  • 30,564
  • 13
  • 72
  • 103
  • I'm Italian and we have a lot of é related trouble... but I got no troubles if: - page encoding is set in UTF-8; - database table collation, table varchar or text collation as utf8_general_ci; and the connection itself in UTF-8. I don't need to iconv nothing – MiPnamic Mar 24 '11 at 14:53
  • I'm Bulgarian and I'm using utf8 a lot :) but the problem is that this particular table is filled by a script that had no `mysq_set_charset` – Teneff Mar 24 '11 at 15:13
  • there isn't a way to "force" the incoming charset of that script? I don't know, I'm just supposing, if mysql force as default the utf-8 charset it could work... – MiPnamic Mar 24 '11 at 15:23
  • Try to look at this, he force UTF-8 into my.cnf http://forums.mysql.com/read.php?103,28072,28072 – MiPnamic Mar 24 '11 at 15:26
  • All I could think of was to replace all these characters with underscore and get a query like `SELECT * FROM table WHERE name LIKE '%t_st%'` – Teneff Mar 24 '11 at 18:23

4 Answers4

2

I have just performed a quick test, and MySQL works as you expect it to work.

Perhaps it's the way you connect to the database, you could try executing SET NAMES 'utf8'; before performing the queries and see if that helps!

Also, please note that mysql_set_charset should take utf8, with no dash! See an example from PHP to be sure!

Cheers!

fsodano
  • 548
  • 2
  • 8
2

Before the query try:

mysql_query("SET NAMES 'utf8'", $conn);

*edits*

Apprently depending on MySQL version you might also be required to use:

mysql_query("SET CHARACTER SET utf8", $conn);

One final note, the database needs to be using the UTF-8 character set, to ensure this:

ALTER <database_name> DEFAULT CHARACTER SET utf8;

*More edits*

After reading your edits I think that this is a issue with your HTML/PHP encoding. On the page submitting the characters ensure that you set the headers properly:

header('Content-Type: text/html; charset=UTF-8');

You should also set this via meta tags:

<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">

Then set the multibyte encoding with the mb_internal_encoding() function:

mb_internal_encoding("UTF-8");

By default PHP uses ISO-8859-1.

tplaner
  • 8,363
  • 3
  • 31
  • 47
  • `mysql_query("SET NAMES 'utf8'", $conn); ` messes up the results that the next query returns – Teneff Mar 24 '11 at 15:04
  • `ALTER DEFAULT CHARACTER SET utf8;` will change and there are 260 000 records in it :) – Teneff Mar 24 '11 at 15:05
  • Seems like your MySQL is actually working properly, the issue sounds like it is the encoding of the HTML/PHP. – tplaner Mar 24 '11 at 15:21
1

try regexp with the desired regular expression like

SELECT * FROM table WHERE name REGEXP #REGULAR EXP HERE#

add9
  • 1,503
  • 3
  • 17
  • 31
1

Have you tried SET NAMES?

mysql_query("SET NAMES utf8");
Andre Backlund
  • 6,843
  • 3
  • 20
  • 27