I have database with MariaDB 5.5.x engine. On database I have table "items":
Table "items"
------------
ID |name | location | ... |
--------------------------
1 |some name1| 3;56;23;15;4; |
-----------------------
2 |some name2| 4;8;90; |
-----------------------
3 |some name3| 6;27;18;87;|
I'm looking way to get ID when I know location_ID fx. "90". All locations are in database separated by semicolon (";"). On Zend I used and it worked:
where("location REGEXP '(^|[[.semicolon.]])" . $id . "([[.semicolon.]]|$)'");
I programming on Slim Framework with MeekroDB Class. My current code in PHP:
$DB = new DB(); //MeekroDB PHP Class
$data = $DB::query("SELECT * FROM items WHERE locations REGEXP '(^|[[.semicolon.]])%s([[.semicolon.]]|$)'" , $id);
$id is integer. I get error:
QUERY: SELECT * FROM items WHERE location REGEXP '(^|[[.semicolon.]])'1'([[.semicolon.]]|$)' ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1'([[.semicolon.]]|$)'' at line 1
Any idea what is wrong?
mysql> SELECT FIND_IN_SET('87', REPLACE('6;27;18;87', ';', ','));
+----------------------------------------------------+
| FIND_IN_SET('87', REPLACE('6;27;18;87', ';', ',')) |
+----------------------------------------------------+
| 4 |
+----------------------------------------------------+
1 row in set (0.00 sec)
That is,
FIND_IN_SET() to searchYour code would be something like
DB::query("SELECT * FROM items
WHERE FIND_IN_SET(%s, REPLACE(locations, ';', ','))",
$id);
MariaDB 5.5.x doesn't have [[.semicolon.]], use a plain semicolon instead:
SELECT * FROM items WHERE location REGEXP '(^|;)8(;|$)'
http://sqlfiddle.com/#!9/0c5cb/1
Before MariaDB 10.0.5 the POSIX 1003.2 compliant regular expression library was used that doesn't have [[.semicolon.]]. From MariaDB 10.0.5 on the PCRE library is used that supports [[.semicolon.]]. https://mariadb.com/kb/en/mariadb/regular-expressions-overview/