Currently, I am utilizing the jQuery Autocomplete plugin 1.1 for a project.
Below is my calling code:
$("#txtCombo").autocomplete("http://www.example.com/autocomplete.php", {
autoFill: false,
width: 550,
minChars: 3,
selectFirst: false
});
The issue lies in the fact that any input entered into the autocomplete field is automatically converted to lowercase when it reaches the php file autocomplete.php.
For instance, if I search for "Smart Boy," the php file will actually receive "smart boy." This behavior is advantageous as it facilitates a case-insensitive database query lookup and returns matching results regardless of letter case.
However, a problem arises when attempting this with Greek characters. For example, upon entering the query "Μικρές Οικιακές Συσκευές," it gets transformed into "μικρές οικιακές συσκευές." Subsequently, executing a like query such as:
"SELECT ID, NAME FROM CATEGORIES WHERE NAME LIKE '%".$query."%'"
Yields no results since the database does not perform case-insensitive searches on Greek letters.
I attempted to address this issue by using the collation keyword:
"SELECT ID, NAME FROM CATEGORIES WHERE NAME LIKE '%".$query."%' COLLATE utf8_general_ci"
Unfortunately, this resulted in an error message stating: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'.
Realizing that the tables are encoded in latin1 charset, attempts to utilize utf8 or latin1_general_ci collations led to errors including:
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'like'
If only there was a way to prevent the autocomplete from converting the query to lowercase, it would effectively resolve the issue at hand.
One question that lingers is why does the plugin automatically convert to lowercase even without specifying such behavior in the calling parameters?
Any suggestions or insights on this matter would be greatly appreciated.