0

I get problem with mysql search LIKE For example in database table city i have record "Rīga"
How you see in database Word "Rīga" have "ī" not "i" letter.
Is it posibble to make in search even if i write with regular "i" letter?
Because this variant isnt working

SELECT * FROM `city` WHERE `title` LIKE '%Riga%'
Romans Leonovs
  • 53
  • 7

2 Answers2

2

It's called Accent insensitive search: you can find a good answer right here! Accent insensitive search query in MySQL

Other answers:

Community
  • 1
  • 1
Sylter
  • 1,622
  • 13
  • 26
  • Yes finally i did , thank you :) `$q1 = mq($db2, "SELECT * FROM city WHERE title LIKE _utf8'%ka%' collate utf8_general_ci");` This fixed everything :) – Romans Leonovs Jun 27 '16 at 20:04
  • sorry for double post but i get another problem. If i write ka its work but if i write more than 2 letters its shows nothing, for example "kar" its shows nothing but when "ka" its shows 2 records, why is that? – Romans Leonovs Jun 27 '16 at 21:14
  • I must say it's pretty strange. I don't have a final answer to this behavior: maybe because the letter with strange accent is not the last one? Try something like `%ka%r%` and see what happen. – Sylter Jun 28 '16 at 09:08
0

You should use the same connection encoding as your database before executing queries.

If you're using PDO, just set the connection encoding as:

$encoding = "utf8"; //replace this value with the character encoding you're using
$pdo = new PDO("mysql:host=host;dbname=my_db;charset=$encoding", $user, $pass);

If you're using MYSQLI, use mysqli_set_charset:

$encoding = "utf8"; //Use your encode here
$con = mysqli_connect("host",$user,$pass,"my_db");
mysqli_set_charset($con, $encoding);

Edit~

You may need also to use Accent insensitive collation (UTF8_GENERAL_CI) in your database. I believe your problem is already solved in the post MySQL charsets and collations: accent insensitive doesn't work

Community
  • 1
  • 1
CarlosCarucce
  • 3,420
  • 1
  • 28
  • 51
  • Nop its not working. Maybe i didn't explain well. I want make a system whats similar google search. For example in database have 2 records Kārsava and Kandava If i make search "SELECT * FROM `city` WHERE `title` LIKE '%ka%'"; its return only Kandava but i want that if he returns Kārsava too. I tried your solution but didnt work – Romans Leonovs Jun 27 '16 at 19:09
  • Updated. Thanks to the references @Sylter 's answer, but using mysql :) – CarlosCarucce Jun 27 '16 at 19:27