I'm trying to set up a database search like this

$tmpn = mysql_query("SELECT * FROM articles WHERE LOCATE(lower('".$sear."'), lower(name))");

The $ sear variable naturally contains the word — but for some reason it is not case-sensitive for it ((please tell me that I don’t have this way I google on a couple of forums, but for some reason it’s looking only for case-sensitive (

I’ll add something that on the local version of the site going into phpMyadmin the Cyrillic in the fields of the tables is displayed normally - but also the base on the hosting issues krakozyabry, http://hostingkartinok.com/show-image.php?id=34e0c4852c5afbef5344ccfdeb0feadb and on the local hosting and the host everywhere in the tables is utf-8_general_ci, the pages of the site are all utf-8, but I see one difference - on my local host I climbed into Apache in httpd.conf and changed the default charset to utf 8 and here it was hosting and do not even know what it costs - maybe this is the case?

  • and for what words does not work? For Russians? what encoding? Does it work for English? - BOPOH
  • @BOPOH I use Russian words but for them it definitely doesn’t work - I don’t know UTF-8 coding for English, but the Russian say Box and box - it doesn’t work for example - dantelol
  • is utf-8 the same file encoding? And when you connect to the database what do you specify? lower supports multibyte encodings, but the default is latin1. Since this does not work for you, it means that something is wrong with the encoding when connecting (it may be that the table doesn’t indicate what is needed, but I’m not sure here). Try instead of this query to write, for example, "SELECT lower('".$sear."') AS my_word, lower(name) AS db_word FROM articles LIMIT 1" and look where it is broken. When connecting, you must specify SET NAMES 'utf8' and create tables with DEFAULT CHARSET=utf8 - BOPOH
  • Yes, it's just that the author has probably scored a Latin one in the database or in a query instead of a Russian letter with the same outline :) There is Russian, there is Latin. So look now for the register :) - Sergey
  • All tables have no tables and utf-8_general_ci stands for - dantelol

2 answers 2

If data are displayed incorrectly in phpMyAdmin, then most likely they were added to the database incorrectly. For example, you did a local database export to a file that was not in UTF-8 encoding (the upload was done before the default encoding was specified in the Apache) and didn’t match when importing the encoding. If the local database LOCATE works as it should - try to transfer the data to the hosting again so that they are already displayed correctly.

  • in fact, the database was initially created in utf-8 but imported only a structure without data, then changed the encoding in Apache, then tried to enter new data but did not succeed - the same result, most likely, the data is entered in the wrong format coding on php before database operations? I tried it like this but didn’t bring any results either: header ('Content-Type: text / html; charset = UTF-8'); mb_internal_encoding ('UTF-8'); mb_http_output ('UTF-8'); mb_http_input ('UTF-8'); mb_regex_encoding ('UTF-8'); - and then the connection to the database and so on has already gone. - dantelol
  • You also need to specify the working encoding after connecting to the database: mysql_query ('SET NAMES utf8 COLLATE utf8_general_ci'); - Darevill
  • this helped both answers to give me something to think about, but here's the idea to insert right after connecting to the database, this is mysql_query ("SET NAMES 'utf8'"); mysql_query ("SET CHARACTER SET 'utf8'"); mysql_query ("SET SESSION collation_connection = 'utf8_general_ci'"); - solved the problem completely Thank you all) - dantelol

First, try to see what happens with the lower field in the query.

 SELECT *, lower(name) AS LowerCase FROM articles

So you will understand the problem with the encoding or not, if the register is lower then try to build the query differently, for example, for example

 SELECT *, lower(name) AS LowerCase FROM articles WHERE LOCATE(lower('".$sear."'), LowerCase)

If it does not help, then look in the direction of setting the encoding base

  • This is how the query was formulated $ tmpn = mysql_query ("SELECT *, lower (name) AS LowerCase FROM articles WHERE LOCATE (lower ('". $ sear. "'), lower (LowerCase))"); but after that, mysql_num_rows started to produce an error Warning: mysql_num_rows () expects parameter 1 to be resource, boolean given in ... something that did not go so well in the query ( - dantelol