-1

I need to search a text field on a database avoiding mismatch for special chars but for the same phrase.

For example, if my search term in DB field is saved as "I lòve mysql ánd query" I would like to match the search for "I love mysql ánd query","I love mysql and query","I löve mysql ánd query",etc.

I was thinking to convert the phrases with a PHP function that I use for url rewrites flattening them out always to "I love mysql and query" but I'm not sure I can flatten them out in the query?

al404IT
  • 1,380
  • 3
  • 21
  • 54

1 Answers1

0

Since your data is already written to the DB with accents, can you try using DB collation to map directly between accented characters:

$connection->query("SET NAMES utf8 COLLATE utf8_general_ci");

You can read more about it here

The page above explaints clearly what this collation will do for you:

mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1
       (c1 CHAR(1) CHARACTER SET UTF8 COLLATE utf8_general_ci);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('a'),('A'),('À'),('á');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c1, HEX(c1), HEX(WEIGHT_STRING(c1)) FROM t1;
+------+---------+------------------------+
| c1   | HEX(c1) | HEX(WEIGHT_STRING(c1)) |
+------+---------+------------------------+
| a    | 61      | 0041                   |
| A    | 41      | 0041                   |
| À    | C380    | 0041                   |
| á    | C3A1    | 0041                   |
+------+---------+------------------------+
4 rows in set (0.00 sec)

You can also test it for youself directly in the DB (test taken from here):

mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a' = 'A', 'a' = 'À', 'a' = 'á';
+-----------+-----------+-----------+
| 'a' = 'A' | 'a' = 'À' | 'a' = 'á' |
+-----------+-----------+-----------+
|         1 |         1 |         1 |
+-----------+-----------+-----------+
1 row in set (0.06 sec)
Alexandre Juma
  • 3,128
  • 1
  • 20
  • 46
  • 1
    this is not going to work alone, I already have a function to flat out my query string in PHP before query the DB but since in DB are save with accents it will not going to work without using some function in query – al404IT Dec 03 '18 at 10:34
  • oh, i didn't understood you had the data stored in the database with the accents. – Alexandre Juma Dec 03 '18 at 10:42
  • Can you check the new answer? I think it feets best your needs (it should support searches for all your characters and it's variations). – Alexandre Juma Dec 03 '18 at 11:14