php - Find multiple words in text from database

Question

I have a String coming from an Android system. I receive that String, corresponding to an address in a php file, and want to check if this address matches one from my database. However, i want it to work whatever the order of the words the user inputs.

At first, i used LIKE as follow:

$address=preg_replace('#\s+#','%',$_POST['address']);
    $address='%'.$address.'%';
    echo $address;
    $list_business = $bdd->prepare('SELECT * FROM business WHERE address LIKE ? OR name_business LIKE ?'); 
    $list_business->execute(array($address,$address));

This actually allows me to get results even if some words are ommited. For example 443 Street would match 443 First Street. But it wouldn't return anything if the user types id as First Street 443. I was thinking about Regex, but is it really adapted for this kind of issue? Or should I use one different Regex per word?

Answer 1

You might want to take a look at third-party search engines that will do this for you quite easily.

Take a look at SOLR, ElasticSearch, and Sphinx, in that order.

You could probably do this with Mysql's full-text search but the search engines above can do it better.

Here's a demo using MySQL's built-in full text search

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sample         |
+----------------+
1 row in set (0.00 sec)

mysql> show create table test.sample;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                       |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sample | CREATE TABLE `sample` (
  `id` int(11) NOT NULL,
  `address` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ftext` (`address`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test.sample;
+----+-----------------------------------------+
| id | address                                 |
+----+-----------------------------------------+
|  1 | 345 Park Avenue New York, NY 10154-0102 |
|  2 | 610 Waring Ave Bronx, New York NY 10467 |
+----+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.sample 
    -> WHERE match(`address`)
    -> against('+new +york +ave' IN BOOLEAN MODE);
+----+-----------------------------------------+
| id | address                                 |
+----+-----------------------------------------+
|  1 | 345 Park Avenue New York, NY 10154-0102 |
|  2 | 610 Waring Ave Bronx, New York NY 10467 |
+----+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.sample
    -> WHERE match(`address`)
    -> against('+ny +park' IN BOOLEAN MODE);
+----+-----------------------------------------+
| id | address                                 |
+----+-----------------------------------------+
|  1 | 345 Park Avenue New York, NY 10154-0102 |
+----+-----------------------------------------+
1 row in set (0.00 sec)

mysql>