0

I'm creating a website design in PHP with aquarium ads - www.akwa-market.pl I wanted the site to be international. I have created a database and tables, one of them is with classifieds entries. I also have a built-in word search. Suppose I typed in the polish word turtle - żółw I run the command:

SELECT FROM * table_name WHERE item REGEXP '[[:<:]]{$keyword}[[:>:]]';

or:

SELECT FROM * table_name WHERE item REGEXP '{$keyword}';

and find a record in the database that contains that word. The problem occurs when I enter a word in Russian, for example Привет The search engine does not show a record with the given Russian word.

After connecting to the database, I call.

mysqli_set_charset($db, "utf8mb4");

mysqli_query($db, "SET NAMES utf8mb4");
mysqli_query($db, "SET CHARACTER SET utf8mb4");

I tried to set in db the string comparison method to utf8mb4_bin or to utf8mb4_unicode_ci but this does not give successful results. It is still possible to search only words containing Latin characters, including diacritics of European languages. I don't know how to set the query to be able to search for words in other languages, e.g. Russian, Chinese or Arabic.

I mean to use it together with REGEXP, preferably to search for several words at the same time, separated by the 'I' sign, i.e. OR, where, for example, the $keyword variable takes the value:

$keyword = 'Привет | Андрей';

P.S. Additionally, I don't know how to solve the case when someone types - polish word tortoise - zółw with z letter without dot, so that the word tortoise - żółw can be searched. Or even written without pressing ALT, e.g. zolw

Can anyone help? The site is almost functioning normally there is no errors given by the server, but the search engine is not working correctly and I don't really know how to solve it. Sorry, but English is my second language.

Edit: Output show create table as user @ysth asked.

CREATE TABLE `items` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `user_name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_id` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `main_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `sub_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `voivodeship` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `town` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `latitude` decimal(11,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `entry_start` timestamp NULL DEFAULT NULL,
  `entry_expire` timestamp NULL DEFAULT NULL,
  `adv_type` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `adv_status` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adv_views` decimal(9,0) DEFAULT 0,
  `users_report` text COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`index`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

as I see now the table description column where the Russian text was saved has different collation utf8mb4_bin, if that is the case of problem sorry for disturbing, I have to check that right now and let You know.

Sylwester Bogusiak
  • 9
  • 3
  • Show output of `show create table table_name;` – ysth Nov 25 '22 at 21:43
  • Take a step back and see if the query works as expected in native MySQL. If so than the issue is with mysqli or PHP, if not than the issue is with schema. Also the keywork variable should be grouped so the word boundaries are on both side. Currently your or `|` would split the word boundaries. This also could be open to SQL injections. Should be binding the value in. – user3783243 Nov 25 '22 at 22:01
  • @ysth I have updated my question. Yes, you point me to the answer. Many thanks I have just changed collation of the description column to utf8mb4_unicode_ci and it's works. Now I can find russian words in description of items. – Sylwester Bogusiak Nov 25 '22 at 22:46
  • @user3783243 Problem with Russian characters is solved. I have not seen before that one column has different collation. Sorry for that. I hope now that be work with other alphabet, like Arabic or Chinese But the second question with finding words without diacritics or partially written with diacritics only is still open. I don't understand what the or I operator is doing with the word boundaries. How it should be bind? Could you share some piece of code? – Sylwester Bogusiak Nov 25 '22 at 22:58
  • Wbat version of MySQL? A number of potentially useful collations were added to MySQL 8.0; `_unicode_` is quite old. – Rick James Nov 26 '22 at 04:45
  • `item REGEXP '[[:<:]]{$keyword}[[:>:]]'` should be `item REGEXP concat('[[:<:]](', :boundvariable, ')[[:>:]]'` then bind `:boundvariable` with the value. – user3783243 Nov 26 '22 at 05:29
  • @RickJames Server version: 10.5.15-MariaDB-10+deb11u1 , Database client version: libmysql - mysqlnd 5.0.12 , PHP version: 7.1.33 I think I know what you mean. In newest version is available utf8mb4_0900_ai_ci where ai mean accent insensitive and case insensitive I have found some info in this topic https://stackoverflow.com/questions/54885178/whats-the-difference-between-utf8-unicode-ci-and-utf8mb4-0900-ai-ci But Im still struggling with this how to find words in database when they are not properly typed. – Sylwester Bogusiak Nov 29 '22 at 18:12
  • "not typed properly" -- see https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored , especially "best practice" and debugging with "SELECT HEX(col)" – Rick James Dec 02 '22 at 20:51

0 Answers0