# Unicode search query in MySQL



## sachin_kothari (Mar 25, 2008)

I am doing in web project in which i take input in a text box in hindi. I have a mysql database which again contains hindi elements. I want to match the user string with that in my mysql database. I did a search query for it, but I am unable to retrieve records from the database. How do I write the query so that i can retrieve elements from database?

Btw, i am doing a project like google suggest, which gives suggestion in hindi. I have managed all other aspects, except the retrieving from the database. I tried it with english and it did work, so the code is ready, only retrieving part in unicode(hindi) is remaining.


----------



## QwertyManiac (Mar 26, 2008)

Prefix N before the string you're passing as a Unicode one.

Like INSERT INTO X VALUES(N'Unicode string')


----------



## rohan (Mar 26, 2008)

Check the collation of your database. Make sure it is set to utf-8 or any other unicode transformation format. I always use utf-8 and everything works perfect for me, searching, inserting etc.

@QwertyManiac: that is the case with MS SQL, not MySQL.


----------



## sachin_kothari (Mar 26, 2008)

Rohan, could you write a sample query to retrieve records from the database. Because i have done everything you said but i am still unable to retrieve records from database.


----------



## rohan (Mar 26, 2008)

I primarily use MySQL along with php, and here is a query:


```
$query  = "INSERT INTO entries(u_id, content, category_id, genre_id, timestamp, orig_author, disp_type) values(";
				$query .= "".$this->auth->u_id.", \"".$content."\", ".$cat.", ".$gen.", ".time().", \"".$orig_author."\", '".$disp_type."')";
```

Now here, all the inputs i get are from a html form. Even if i type in anything that is in Unicode, it searches it properly. The thing is that.. a query doesn't have much to do with character encoding in MySQL. it's how your database is set.. For example, the complete parameters of my table are:


```
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
```

this might help:

*dev.mysql.com/tech-resources/articles/4.1/unicode.html
*dev.mysql.com/doc/refman/5.0/en/charset-unicode.html


----------



## sachin_kothari (Mar 27, 2008)

rohan bro, the query you have displayed is for inserting records into database. i have no problems inserting records into database, my problem is to retrieve them back. would be a great help if you could solve his problem for me.


----------



## victor_rambo (Mar 27, 2008)

Will this help?

```
$sql = 'SELECT * FROM `table` WHERE `column` LIKE CONVERT(_utf8 \'$searchString\' USING latin1) COLLATE latin1_general_ci';
```


----------



## DigitalDude (Mar 27, 2008)

aviod using innodb engine unless you have a lot of transactions and have performance issues with myisam... innodb uses lot of memory 

btw its better to set your db table types directly instead to hardcode it in the query unless its specifically necessary 

if you have your table set for unicode theres nothing to worry in query...



_


----------



## sachin_kothari (Mar 27, 2008)

rohan_shenoy said:


> Will this help?
> 
> ```
> $sql = 'SELECT * FROM `table` WHERE `column` LIKE CONVERT(_utf8 \'$searchString\' USING latin1) COLLATE latin1_general_ci';
> ```


 I am using UTF8 and COLLATE as utf8_unicode_ci. So that's no problem. 



DigitalDude said:


> aviod using innodb engine unless you have a lot of transactions and have performance issues with myisam... innodb uses lot of memory
> 
> btw its better to set your db table types directly instead to hardcode it in the query unless its specifically necessary
> 
> ...


Thanks for that tip. Will keep that in mind.

The problem gets a bit clearer.
When i type anything into the text box, for eg. if i type 'स्' i get '%u0938%u094D' and i pass this to the query. How can i write the code such that i can pass 'स्' to the query? Because when i hardcoded my query with 'स्' in phpmyadmin i got my intended records.

So the problem now is not the query but the conversion form '%u0938%u094D' to 'स्'.


----------



## DigitalDude (Mar 27, 2008)

I guess this part will be apt for you



> *Working with UTF-8 on the Web*
> 
> Ignoring older (and badly implemented) browsers for a second, handling UTF-8 data on the web is quite simple. You just need to indicate in the header and/or body of your document the character set, like so (using PHP):
> 
> ...


 
quoted from *dev.mysql.com/tech-resources/articles/4.1/unicode.html (the link rohan gave)

and what version of mysql r u using ? the above is for 4 and 4.1

if you are using 5 check this page *dev.mysql.com/doc/mysql/en/charset-metadata.html




and one more point.. I dont know how this might help you but I think there might be some connection

you must be familiar with phpMyAdmin... it requires 'mbstring' php extension to work with utf-8 characters so mbstring might also have a role to play 



_


----------

