Laravel query find comma separated and normal string

Question

I got a column called fac that values can be a single number like 2 and also can be comma separated like 2,4,5

I want to get data from database via api. Also value can be a single or comma separated too. so user can search like these:

localhost/api/search {fac: 1}

Or

localhost/api/search {fac: 1,4,5}

I used find_in_set but look like it not working in this case:

$fac = request('fac');

->whereRaw("find_in_set({$fac}, fac)")->get();

If I run this localhost/api/search {fac: 1} it return nothing, if I run localhost/api/search {fac: 1,4,5} it give me error:

Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'find_in_set'

Goal:

If user search 1 it should return all data that contains 1, if search 1,7 should return all data that contains 1 and 7

Give this a try ->whereRaw("FIND_IN_SET(" . $fac . ", fac)")->get(); — Digvijay
– Digvijay, Commented Nov 3, 2019 at 9:21
Never store anything comma separated. Make your table to be in `st normal form with only atomic values for each column. — mighty_dev
– mighty_dev, Commented Nov 3, 2019 at 12:04
@vivek_23 Why? is there any risk or security issue? | after all, if user select multiple option in front, so how you store values in database? — Jack The Baker
– Jack The Baker, Commented Nov 3, 2019 at 12:58
@tourtravel Then it's a one to many relationship. The reason I said to store values in atomic form is because you will find it a lot easier to maintain. Read here about 1st normal form and read here about Laravel one to many relationship. — mighty_dev
– mighty_dev, Commented Nov 3, 2019 at 13:04

Yasin Patel · Accepted Answer · 2019-11-03 10:13:54Z

2

First, convert your comma-separated string to an array. And create raw query.

$fac = request('fac'); // single value or comma saperated string
$data = explode(',',$fac);
$str='';
$i=1; // to append AND in query

foreach ($data as $key => $value) {
  $str .= 'FIND_IN_SET("'.$value.'" ,fac)';
  if($i < count($data)){
    $str .=' AND '; // use OR as per use
  }
  $i++;
}

Now $str will contain raw query

FIND_IN_SET("1", fac) AND FIND_IN_SET("7", fac)

and you can use in eloquent.

->whereRaw($str)->get();

edited Nov 3, 2019 at 10:13

answered Nov 3, 2019 at 10:03

Yasin Patel

5,7818 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jack The Baker Over a year ago

I got error:

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'FIND_IN_SET' (SQL: select * from

posts` where title like %% and user_id = and FIND_IN_SET("4" fac) AND FIND_IN_SET("2" fac)) in file api\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 665`

Yasin Patel Over a year ago

comma was missing

Yasin Patel Over a year ago

in user id there is nothing ? and also for title ?

Jack The Baker Over a year ago

Yes, comma fixed the issue, but it return nothing as data

Jack The Baker Over a year ago

I changed $str .=' AND '; =>to=> $str .=' OR '; problem fixed :) thanks again

|

Collectives™ on Stack Overflow

Laravel query find comma separated and normal string

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related