Amit Amit - 4 months ago 15
SQL Question

Find array wise ids from comma separated ids in mysql php

i have one table which contains multiple comma separated ids with multiple fields like:

enter image description here

i need some proid which i have already pass using array in mysql

proid = array([0]=>51,[1]=>54,[2]=>8)


i want that rows which contains 51,54 & 8
proid
from the table field which is in comma separate proids

51,52,53,54,2,3,4,5,8,9,11,55,13,14,15,16,17,18,1,...


here i try
FIND_IN_SET
but not work properly in select query, can see below

SELECT * FROM `tbl_request` WHERE FIND_IN_SET('51,54,8', `proid`) > 0


can anyone help me out please,

thanks in advance

Answer

FIND_IN_SET() function take first parameter as a complete string and compare it with the second parameters. If you pass 51 as first param then SQL will return you the results. But you try for '51,8' then it will treat it as a one string not two, and it will search for complete '51,8'. So you need to as Jothi mentioned in comments

SELECT FIND_IN_SET('51', `proid`) > 0 or FIND_IN_SET('54', `proid`) > 0 or FIND_IN_SET('8', `proid`) > 0; 

Reference: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set, http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php

$sql = 'SELECT * FROM `tbl_request` WHERE ';
$items = array(51,54,8);
foreach($items as $item){
    $where[] = "FIND_IN_SET('$item', `proid`) > 0";
}
if(is_array($where)){
    $sql.= implode(' OR ', $where);
}else{
    $sql.= '1';
}
Comments