MySQL 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?

explode town string on `/`, build individual `like` clauses with the exploded parts, build new sql string... or just normalize the data in the tables — Marc B, May 28 '15 at 18:08
@MarcB table isn't fully normallised, I understand. However, could you give me an example of the exploded query? — David Passmore, May 28 '15 at 18:09
Vote for normalizing. This way you are (obviously) limited while searching database. — Whirlwind, May 28 '15 at 18:09
it'd end being `where foo like '%poole%' (and/or, depending on your needs) foo like '%wallasey%'`, e.g. one `like` clause for every town name in your search string. — Marc B, May 28 '15 at 18:18
Only problem with that is both `POOLEVILLE` and `POOLE` would match. Yuck. Also, what if a user enters a town that actually has multiple names separated by a slash (e.g. using country `Netherlands/Holland`)? It's not likely, but possible. See also this question: http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — Mike, May 28 '15 at 18:22

score 3 · Answer 1 · answered May 28 '15 at 18:18

This is an iteration of an often-asked class of questions: How do I select on a single datum, if I have more than one in a field?

The answer, as always, is: You don't.

There are many reasons for that, but one of the most important is performance: Basically a LIKE '%...' can't use an index. That might be ok with a handful of test rows, but it quickly becomes a problem when scaling.

The only reliable ways are to

either normalize your data
or use a fulltext index

In your case I'd strongly vote for normalization: Create a towns table, then link it to the players via a join table. You can now search for any town with full index use, finding the players through the join.

Thanks for the input, the example I am giving is a condensed version of my table, the data is out of my control as is imported from a csv, so I have no way of normalising. — David Passmore, May 28 '15 at 18:20

score 3 · Accepted Answer · edited May 28 '15 at 18:29

3

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

edited May 28 '15 at 18:29

David Passmore

6,089
4
46
70

answered May 28 '15 at 18:28

Christian A.M.

151
2
10

We only have `/` fortunately! – David Passmore May 28 '15 at 18:29

score 1 · Answer 3 · edited May 28 '15 at 18:25

1

As Marc B stated, using explode.

<?php
$array = explode("/",$global->getPlayerTown());

foreach($array as $Town){
  $list = $list ."'%" .$Town ."%', ";
}

$SQL = "SELECT * FROM `table` WHERE `Town` LIKE ANY(" .$list .")";
?>

Please go the smart route and normalize your data. This idea may work, but that doesn't mean it is the best choice.

edited May 28 '15 at 18:25

David Passmore

6,089
4
46
70

answered May 28 '15 at 18:21

Ryan_W4588

648
3
13
32

2

Side note: You're probably better off linking to the official docs than w3schools: http://php.net/explode – Mike May 28 '15 at 18:24
@Mike yes I am, thanks for catching that haha. Fixed it. – Ryan_W4588 May 28 '15 at 18:25
@Ryan_W4588, just tried this and it returns an empty result? – David Passmore May 28 '15 at 18:31

score 0 · Answer 4 · answered May 28 '15 at 18:22

You could explode the towns, then loop through them and build the query like so:

$towns = explode('/', $global->getPlayerTown());
$first = true;
$like_sql = '';
foreach($towns as $town) {
    $like_sql .= $first ? ' WHERE ' : ' OR ';
    $like_sql .= "`Town` LIKE '%{$town}%'";
    $first = false;
}
$query = "SELECT * FROM `table` {$like_sql}";

However I would recommend you normalise your data, and have a separate towns table, with a user_town pivot table.

MySQL WHERE LIKE multiple criteria

4 Answers4

Linked

Related