MYSQL Collate in QUERY

Question

Hi I am trying to make search on my web which is looking in DB with

CHARACTER SET utf8 COLLATE utf8_slovak_ci

Table is also utf8_slovak_ci . In table called knihy (books) I have fields Autor1 Autor2 Autor 3 Autor4 /Author 1 etc./ Here is query which I am using for searching:

$q = $this->db->query("SELECT * FROM (`knihy`) 
              WHERE `stav` = 1 
              AND (Autor1 LIKE '$vyraz' 
              OR Autor2 LIKE '$vyraz' 
              OR Autor3 LIKE '$vyraz' 
              OR Autor4 LIKE '$vyraz') 
              ORDER BY `id` desc LIMIT $limit OFFSET $offset ");

Lets say I have Book in my table with Autor1 /Author1/ with name "Šilíoá" if I am looking for "Šilíoá" It will be found also It will be found if I am looking for "Šilioa" but if I am looking for "silioa" nothing will be found. So problem is in char "Š" and "š" how can I fix this problem? I found a lot tutorials on the internet but It still not work...

score 2 · Accepted Answer · answered Sep 14 '12 at 14:11

2

You can fix it by setting table charset to utf8, collation to utf8_general_ci or utf8_unicode_ci. and you also need to set,in your code(connection string), charset to utf8.

Example for connection string:

string cs = @"server=localhost(or IP address);userid=root;password=xxx;database=table_name;charset=utf8";

answered Sep 14 '12 at 14:11

Sylca

2,523
4
31
51

I did it like: "ALTER TABLE `knihy` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" but it still doesn`t works :( – Geril Sep 14 '12 at 14:16
1

You might require to do that at column level as well by firing query like ALTER TABLE `knihy` CHANGE `Autor1` `Autor1` VARCHAR(20) CHARSET utf8 COLLATE utf8_general_ci; – jsist Sep 14 '12 at 14:22

MYSQL Collate in QUERY

1 Answers1