Using Cyrillic in MySQL Search

Question

Implemented a search in mySQL, until I encountered the problem of the impossibility of searching using Cyrillic.

$str = '%'.$_GET['s'].'%'; $pdo = new PDO(подключение); $sql = "SELECT * FROM clients WHERE id LIKE ? OR order_id LIKE ? OR datetime LIKE ? OR name LIKE ?"; $sth = $pdo->prepare($sql); $sth->execute(array($str, $str, $str, $str)); while($row = $sth->fetch()) { $clients[] = $row['id']."|".$row['order_id']."|".$row['datetime']."|".$row['name']; }

An Illegal mix of collations for operation 'like' error occurs. I also tried to add, but did not give anything.

 datetime LIKE ? COLLATE utf8_unicode_ci

In mySQL settings

 mysql> SHOW VARIABLES LIKE 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +----------------------+-----------------+

Using datetime is necessary, but errors occur with it. Please suggest how to fix the error :)

stackoverflow.com/questions/18629094/… the thing is probably in datetime
@ Alexey Shimansky in the question indicated that this is due to the datetime :) The link has already been seen earlier, suggesting that there is already a solution.
I didn’t see something just this message about datetime Then it’s not about Cyrillic ... maybe then you should first check that there’s a get request and from that either execute the request without a datetime or concatenate it to the request
And here is Cyrillic, if you have a solid manipulation with numbers in the request?
@Alexey Shimansky Not all columns are shown in the code, there are several Cyrillic ones.
Tell me, what characters to check the input string ($ _GET)?

Accepted Answer · 2015-12-24T11:03:17

You can try to check the GET parameter for a number and, depending on this, carry out the remaining actions. But, most likely, it is necessary to divide the logic into two different requests. At a minimum, because if this number means searching by name, it’s definitely useless to do what will be just an extra load.

Especially with the LIKE %SEARCH_STRING% method, the indices of the table in the database are not used.

Similarly, if the search is not a number, then doing a LIKE query on numbers is stupid.

Example:

 $str = '%'.$_GET['s'].'%'; $getIsNumeric = is_numeric($_GET['s']) || is_numeric(strtotime($_GET['s'])); // выдаст true если придет id или datetime $pdo = new PDO(подключение); $sql = "SELECT * FROM clients WHERE name LIKE ?"; if (!$getIsNumeric) { $sth = $pdo->prepare($sql); $sth->execute(array($str)); } else { $sql = "SELECT * FROM clients WHERE id LIKE ? OR order_id LIKE ? OR datetime LIKE ?" $sth = $pdo->prepare($sql); $sth->execute(array($str, $str, $str)); } while($row = $sth->fetch()) { $clients[] = $row['id']."|".$row['order_id']."|".$row['datetime']; }

In general, it seems to me that searching by id looks strange. I think this could potentially cause hacking. Since I, as a burglar, can know that Vasya Pupkin has id 111, this facilitates the way of hacking. order_id too.

Not sure that searching through these fields is the right decision. It is worth looking for information on this topic just in case.

Thanks for the tip with the separation of numeric and text values.
ID must be used, the more the search is available only to the administration.

Using Cyrillic in MySQL Search

1 answer 1

More articles: