MYSQL replies differently for similar queries

Question

I am using xamp server:

when I am running the select query to get data as

SELECT `id`, `relation`, `member_id`, `Relative_id` 
    FROM `relationship` WHERE `relation` = 'माँ'

All works fine

But when running below example of queries I get 0 results.

Example 1 :

SELECT `id`, `relation`, `member_id`, `Relative_id` 
    FROM `relationship` WHERE `relation` = 'बेटी'

Example 2 :

SELECT `id`, `relation`, `member_id`, `Relative_id` 
    FROM `relationship` WHERE `relation` = 'पिता'

the image regarding db table

click here Table structure:table structure

Agreed. https://stackoverflow.com/a/40698282/57191 might help — cwallenpoole, Jun 27 '17 at 17:16
I mean,you are using different filters as what you wrote in Hindi is first daughter, then mother, then husband. What do you expect then? you are giving different filter parameters. I cant imagine anyone being a daughter, mother and husband all in one person, not even in India. — Arminius, Jun 27 '17 at 17:17
'member_id' is for a person but the 'relative_id' for his different relatives and the 'relation' is how member related with that specific relative — AJAY RAGHUVANSHI, Jun 27 '17 at 17:25
what character set are you using in your db? Where are you calling these queries from? Is this behavior replicated when you run the queries in phpmyadmin? — tony gil, Jun 27 '17 at 18:13
I am using utf8 as character set. I am calling this queries in php . and when I use phpmyadmin same behavior — AJAY RAGHUVANSHI, Jun 27 '17 at 18:19

tony gil · Answer 1 · 2017-06-27T18:26:26.333

2

You must use a Unicode character set (utf8_unicode_ci works fine), declare the field as VARCHAR and use LIKE, instead of =

SELECT `id`, `relation`, `member_id`, `Relative_id` 
FROM `relationship` WHERE `relation` LIKE 'पिता';

Tested and working.

edited Jun 27 '17 at 18:26

answered Jun 27 '17 at 18:20

tony gil

1

changing character set to 'utf8_unicode_ci' works fine, even ' = ' also works – AJAY RAGHUVANSHI Jun 28 '17 at 02:52
1

@AJAYRAGHUVANSHI welcome to SO. If any of the answers on this post solves your problem, it is usual to award it as `correct answer`. – tony gil Jun 28 '17 at 16:39

score 1 · Answer 2 · answered Jun 27 '17 at 17:17

1

You need to use UTF-8 encoding to compare language.

answered Jun 27 '17 at 17:17

Ravi

I used mysqli_set_charset($conn,"utf8"); in my php code but no effect – AJAY RAGHUVANSHI Jun 27 '17 at 17:37
1

could you share your table structure as well ? – Ravi Jun 27 '17 at 17:44

2 Answers2