php mysql search with special character

Question

I have a table in mysql DB which contains special character like Ø,Æ,etc. I cannot find these fields when i run a search with php. but when i run the same sql in phpmyadmin, i get results. this is the table structure:

CREATE TABLE IF NOT EXISTS `clientinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adresse` varchar(160) NOT NULL,
  `gatenavn` varchar(20) NOT NULL,
  `husnr` varchar(20) NOT NULL,
  `bokstav` varchar(2) NOT NULL,
  `postnr` varchar(20) NOT NULL,
  `poststed` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=398 ;

This is a sample query:

SELECT * FROM clientinfo WHERE gatenavn = 'EKRAVEIEN' AND husnr = '1' AND postnr = '2010' AND poststed = 'STRØMMEN'

when i run this query in phpmyadmin, i get result; but don't get when i run with php. I am using mysqli. need some help.

Check the charset of the html document, and set it to utf8; USe `AddDefaultCharset UTF-8` in your httpd.conf Set `default_charset = "utf-8";` in your php.ini file — cipher, Jun 26 '13 at 14:59
possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Álvaro González, Jun 26 '13 at 15:03

martinstoeckli · Answer 1 · 2013-06-26T15:15:40.063

Tell your connection instance, to deliver UTF-8, before making queries. In MySqli you can call the function set_charset(), afterwards the connection object will deliver UTF-8.

Calling this function makes you independent of the database configuration, if necessary the connection will convert the returned data. Of course it is fastest if no conversion is necessary, so adjusting the configuration is a good thing too.

// tells the mysqli connection to deliver UTF-8 encoded strings.
$db = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);
$db->set_charset('utf8');

score 2 · Answer 2 · answered Jun 26 '13 at 14:58

2

Try setting :

SET NAMES utf8;

before your query in the same mysql session

answered Jun 26 '13 at 14:58

Stephan

8,000
3
36
42

actually it works when i use normal php mysql_query functions. but i am using mysqli. for this i have tried: $mysqli->set_charset('utf8'); but does not work. – Imrul.H Jun 26 '13 at 16:16
1

@Imrul.H - Did you also call `set_charset('utf-8')` before you inserted data to the database? If you query the data with UTF-8 which was inserted in another encoding, it will not work of course. Try to add a new row and make a query afterwards. – martinstoeckli Jun 26 '13 at 20:07
Hi again, when i am using set_charset('utf-8') before insert, then "STRØMMEN" inserts as "STR". i am using Collation as "utf8_general_ci" in mysql database. – Imrul.H Jun 27 '13 at 07:49
so the problem is at insert not select? – Stephan Jun 27 '13 at 08:02
1

@Imrul.H - The collation doesn't matter in this case, it is only used for ordering. If the string is not inserted correctly, i suspect, that your PHP page is not correctly encoded in UTF-8. Maybe you want to have a look at my small [article](http://www.martinstoeckli.ch/php/php.html#utf8), it shows the important points for working with UTF-8. – martinstoeckli Jun 27 '13 at 10:19

score 0 · Answer 3 · answered Jun 26 '13 at 15:04

If you have access to your MySQL configuration file, set these settings to my.cfg:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

score -1 · Answer 4 · answered Jun 26 '13 at 15:01

-1

Try using htmlspecialchars php function.

string htmlspecialchars ( string $string , int $flags = ENT_COMPAT | ENT_HTML401
                         , string $encoding = 'UTF-8' , bool $double_encode = true)

You can change the encoding info of your string. Eg KOI8-R for Russian symbols

answered Jun 26 '13 at 15:01

Marcassin

1,386
1
11
21

what good would that do? If `Ø` is what's literally in the database, then searching for `&Oring;` is still not going to find anything. – Marc B Jun 26 '13 at 15:04
Sorry, I thought about store et search using htmlspecialchars. Forget it if others solution (set charset) work. – Marcassin Jun 26 '13 at 15:15

score -1 · Answer 5 · answered Jun 26 '13 at 15:01

This could be a issue with your php.ini file, your Apache Webserver, or charset of your HTML Document or your MySQL

Couple of things to to:

Always ensure that your charset is set to utf8 in your html meta tags

Set

default_charset = "utf-8";

in your php.ini file

And add

AddDefaultCharset UTF-8

to your httpd.conf if you are outputting unicode chars

php mysql search with special character

5 Answers5