Search code examples
phpmysqlsql-like

mysql like values with comma

Basically, the setup of the table is something like this:

+-----------+----------+-------------+
|   land    |   city   |  perimeter  |
+-----------+----------+-------------+
|  America  |  Kansas  |      1      |
|  Britain  |  Berlin  |      4      |
|  Japan    |  Tokyo   |      5      |
+-----------+----------+-------------+

My current query:

$query = "SELECT land, city, perimeter FROM agents WHERE land LIKE ? OR city LIKE ? OR perimeter LIKE ?";
$params = array("%China%","%Kansas%","%6%");

This query works, with this it will return America,Kansas,1. However if my params will be equal to:

$params = array("%China%","%Beijing,London,Kansas,Bali%","%6%");

This will not return anything. How can I use LIKE in values separated with commas to match at least if one item exists in those comma separated values.

Solution

  • Instead of LIKE use REGEXP (more information here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html).

    $query = "SELECT land, city, perimeter FROM agents
   WHERE land REGEXP ? OR city REGEXP ? OR perimeter REGEXP ?";
$params = array("^China$","^Beijing|London|Kansas|Bali$","^6$");