php - How get data from MariaDB using semicolon delimiter -


good morning!

i have database mariadb 5.5.x engine. on database 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 id when know location_id fx. "90". locations in database separated semicolon (";"). on zend used , worked:

where("location regexp '(^|[[.semicolon.]])" . $id . "([[.semicolon.]]|$)'"); 

i programming on slim framework meekrodb class. current code in php:

$db = new db(); //meekrodb php class $data = $db::query("select * items locations regexp '(^|[[.semicolon.]])%s([[.semicolon.]]|$)'" , $id); 

$id integer. error:

query: select * items location regexp '(^|[[.semicolon.]])'1'([[.semicolon.]]|$)' error: have error in sql syntax; check manual corresponds mariadb server version right syntax use near '1'([[.semicolon.]]|$)'' @ line 1

any idea 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,

  1. change semicolon comma
  2. find_in_set() search
  3. test against 0 see if present. 0=false, >0 = true.

your code like

db::query("select * items            find_in_set(%s, replace(locations, ';', ','))",       $id); 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -