Geek Answers Handbook

MySQLi WHERE LIKE multiple criteria

I have the following sample table and attributes:

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

I use the following SQL statement in PHP to retrieve rows:

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

Given the POOLE criteria, the database returns:

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

However, when using the POOLE/WALLASEY query returns:

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

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

+5
source share
4 answers
 SELECT * FROM `table` WHERE `town` REGEXP 'POOLE|WALLASEY';

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

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

Regarding performance, I'm not sure how REGEXP is, unlike LIKE.

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

+3
source

This is an iteration of a frequently asked class of questions: how to select a single data item if I have several fields in a field?

The answer, as always, is this: you do not.

There are many reasons for this, but one of the most important is performance: in principle, LIKE '%...' cannot use an index. This might be fine with a few test lines, but it quickly becomes a problem when scaling.

The only reliable ways are

  • either normalize the data
  • or use full-text index

In your case, I will strongly vote for normalization: create a towns table, and then bind it to the players through the connection table. Now you can search for any city with full indexing, finding players through the connection.

+2
source

As stated by Mark B, using explode .

 <?php $array = explode("/",$global->getPlayerTown()); foreach($array as $Town){ $list = $list ."'%" .$Town ."%', "; } $SQL = "SELECT * FROM `table` WHERE `Town` LIKE ANY(" .$list .")"; ?>

Follow the smart route and normalize your data. This idea may work, but that does not mean that it is the best choice.

+1
source

You can blow up the cities, then scroll through them and build the query as follows:

 $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 that you normalize your data and have a separate city table using the user_town pivot table.

0
source

More articles:


All Articles