PHP-MySQL - MATCH AGAINST don't work properly

Question

I have a problem getting a query to work. I use PHP 8 and MySQL 5.7.36

What i need is let the user type a place (city,country or region) and show all corresponding places while typing.

The table name is : places and i have 4 fields : id, name, name_fr, alternate_name

So i want that if a user wants to find 'Belgium' so if he wrote it in French(belgique) of in English(Belgium), i want that Belgium is displayed... so i make the queries on 'alternate_field' field.. here what i did :

At first i made this query :

$dbcon->query("select name from places where (alternate_name like '%$q%') LIMIT 25");

It works perfectly but it was too slow..

So i found that i can use MATCH and AGAINST.. So i tried this :

1- at first, i alter the table with :

ALTER TABLE places ADD FULLTEXT(alternate_name);

2- then i tried this :

$dbcon->query("select name from places where MATCH(alternate_name) AGAINST('".$q."'  IN BOOLEAN MODE) LIMIT 25");

and it was very fast but the suggestion appears only when i type the full name... Ex.. if i'm looking for 'belgium'.. if i start typing "belg" nothing is shown... but belgium is found when i fully wrote the place name... So that is not what i want...

3- Then i tried this :

$dbcon->query("select namefrom places where MATCH(alternate_name) AGAINST('+*".$q."*'  IN BOOLEAN MODE)  LIMIT 25");

and I thought that I had finally found because it was working..... until i add 'space' or hyphen (-) in the place name... like : 'Los angeles' or 'New York' or 'Winston-Salem'

When the place name has a hyphen, i receive this error : syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

And when the place name has a space in the name... like 'Los Angeles' its shows all places with in the name 'los' and all places with in the name 'angeles'.. of course 'Los Angeles' is displayed but with a lot other places... that not corresponding to 'Los Angeles'

So what i'm doing wrong ?

Thank you so much ! Have a great day !

Answer 1

Firstly you are using a mixture of single and double quotes it makes code messy. If you are concatenating text with variable it's better approach to use single quotes. Double quotes tells php to search variable in the text.

$dbcon->query('SELECT name FROM places WHERE MATCH(alternate_name) AGAINST('+*' . $q . '*'  IN BOOLEAN MODE) LIMIT 25');

I've created similar table from your question and it works totally fine with your query.

SELECT * FROM `places` WHERE MATCH(`alternate_name`) AGAINST("+*los*" IN BOOLEAN MODE);

However, you want to use hyphens as well so you need to replace hyphen with space so you can use php preg_replace or str_replace function.

str_replace("-"," ","Los-Angeles");
// Los Angeles

Finally for your example query you should change your code as below.

Query Updated.

$dbcon->query('SELECT name FROM places WHERE MATCH(alternate_name) AGAINST("+*'. str_replace("-"," ",$q) .'*" IN BOOLEAN MODE) LIMIT 25;');

Also check what I wrote for your question maybe it helps a little more.

Create table

CREATE TABLE `test2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cities` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `cities` (`cities`)
)

I have more than 10000 city names but sharing here a few of them.

INSERT INTO `test2`(`cities`) VALUES ("Tokyo");
INSERT INTO `test2`(`cities`) VALUES ("Jakarta");
INSERT INTO `test2`(`cities`) VALUES ("Delhi");
INSERT INTO `test2`(`cities`) VALUES ("Manila");
INSERT INTO `test2`(`cities`) VALUES ("São Paulo");
INSERT INTO `test2`(`cities`) VALUES ("Seoul");
INSERT INTO `test2`(`cities`) VALUES ("Mumbai");
INSERT INTO `test2`(`cities`) VALUES ("Shanghai");
INSERT INTO `test2`(`cities`) VALUES ("Mexico City");
INSERT INTO `test2`(`cities`) VALUES ("Guangzhou");
INSERT INTO `test2`(`cities`) VALUES ("Cairo");
INSERT INTO `test2`(`cities`) VALUES ("Beijing");
INSERT INTO `test2`(`cities`) VALUES ("New York");
INSERT INTO `test2`(`cities`) VALUES ("Kolkāta");
INSERT INTO `test2`(`cities`) VALUES ("Moscow");
INSERT INTO `test2`(`cities`) VALUES ("Bangkok");
INSERT INTO `test2`(`cities`) VALUES ("Dhaka");
INSERT INTO `test2`(`cities`) VALUES ("Buenos Aires");
INSERT INTO `test2`(`cities`) VALUES ("Ōsaka");
INSERT INTO `test2`(`cities`) VALUES ("Lagos");
INSERT INTO `test2`(`cities`) VALUES ("Istanbul");
INSERT INTO `test2`(`cities`) VALUES ("Karachi");
INSERT INTO `test2`(`cities`) VALUES ("Kinshasa");
INSERT INTO `test2`(`cities`) VALUES ("Shenzhen");
INSERT INTO `test2`(`cities`) VALUES ("Bangalore");
INSERT INTO `test2`(`cities`) VALUES ("Ho Chi Minh City");
INSERT INTO `test2`(`cities`) VALUES ("Tehran");
INSERT INTO `test2`(`cities`) VALUES ("Los Angeles");
INSERT INTO `test2`(`cities`) VALUES ("Rio de Janeiro");
INSERT INTO `test2`(`cities`) VALUES ("Chengdu");

PHP code

$q = 'los';
$result = $conn->query('SELECT cities FROM test2 WHERE MATCH(cities) AGAINST("+*'. str_replace("-"," ",$q) .'*" IN BOOLEAN MODE) LIMIT 25;');

foreach ($result as $key => $res) {
  echo $res['cities'] . '<br>';
}

Result

Los Angeles
León de los Aldama