MySQLi WHERE LIKE multiple criteria

Question

I have the following example table and attributes:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 1 |      POOLE     |
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
| Name 4 |      POOLE     |
---------------------------

I am using the following SQL statement in PHP to retrieve rows:

SELECT * FROM `table` WHERE `Town` LIKE '%".$global->getPlayerTown()."%'

Given the criteria POOLE the database returns:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 1 |      POOLE     |
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
| Name 4 |      POOLE     |
---------------------------

However when using the criteria POOLE/WALLASEY the query returns:

---------------------------
|  Name  |       Town     |
---------------------------
| Name 2 | POOLE/WALLASEY |
| Name 3 | POOLE/WALLASEY |
---------------------------

How do I intelligently tell the PHP to split the string into separate criteria (i.e. POOLE and WALLASEY) in one query, so that the query retrieves all rows?

Answer 1

SELECT * FROM `table` WHERE `town` REGEXP 'POOLE|WALLASEY';

This will match any rows that has one or more instances of POOLE or WALLASEY.

As to the PHP side, depending on how many kinds of separators ('/' in this case) you have in your dataset, it can get rather messy rather quickly. But replace '/' with '|' in getPlayerTown() would seem to be one way of doing it.

As to performance, I'm not sure how REGEXP is as opposed to LIKE.

https://dev.mysql.com/doc/refman/5.7/en/regexp.html