Array value to check with comma separated values in table and return all row if single match with values

Question

I have a array i.e.

$frame = Array(
   [0]=1,
   [1]=2,
   [2]=4 
);

and I want to get value form table "standard_product" where "frame_category" is a column name, this column value are look like frame_category
4,7
1,3,4
6,7,8
return all row if any value of $frame match with any value of frame_category column. Here require result is:
4,7
1,3,4

My effort to resolve this is:

Standard_product::Where('frame_category', 'LIKE', '%' . $frame . '%')->get()->toArray();
(SELECT * FROM  `standard_products` WHERE  `frame_category` LIKE  "%".$frame."%");

but i will not return expected result. Please help me.

Looks like your array has duplicate keys..... which can't exist in PHP — Mark Baker
– Mark Baker, Commented Nov 9, 2015 at 10:06
Bad database design, but FIND_IN_SET().... otherwise you'll get results for that match 14, 42, etc as well as those that match 4.... and as the db can't use indexes for this type of search, it won't ever be a fast query — Mark Baker
– Mark Baker, Commented Nov 9, 2015 at 10:13

Abdulla Nilam · Accepted Answer · 2015-11-09 10:08:47Z

1

remove toArray() last in the query

Standard_product::where('frame_category', 'LIKE', '%'.$frame.'%')->get();

or

Standard_product::like('frame_category', '%$frame%')->get();

Or

$category = DB::table('Standard_product')
          ->where('frame_category', 'like', '%$frame%')
          ->get();

Where Clauses In laravel

answered Nov 9, 2015 at 10:08

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

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

6 Comments

Aman Singh Over a year ago

thanks for your solution but it will not return required result.

Aman Singh Over a year ago

In above example i was got null array as a result.

Abdulla Nilam Over a year ago

is database have data??

Aman Singh Over a year ago

It will work only if sub string of $frame found in database column

Aman Singh Over a year ago

But i need to get all row if any of the value of $frame match with database column

|

Abhishek Sharma · Accepted Answer · 2015-11-09 10:05:24Z

1

use MySQL IN

$ids = join(',', $frame);
WHERE  frame_category IN ($ids)

Passing Your to array to MySQL like that

answered Nov 9, 2015 at 10:05

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

Comments

Krishna Gupta · Accepted Answer · 2015-11-09 10:11:46Z

1

$frame = Array( [0]=1, [1]=2,[0]=4 );
foreach ($frame as $val) {
$query = mysql_query("SELECT * FROM  `standard_products` WHERE find_in_set(".$val.", frame_category)");
while($row = mysql_fetch_assoc($query)){
$result[] = $row;
}
}

answered Nov 9, 2015 at 10:11

Krishna Gupta

6854 silver badges15 bronze badges

2 Comments

Aman Singh Over a year ago

Thanks for your solution but this is too heavy for me. because it will take much more time to run.

Mark Baker Over a year ago

If it takes too long to run, then normalize your database properly and you won't need expensive FIND_IN_SET queries.... you designed your database badly in the first place.... the real fix is to correct the design, not use an inefficient workround

Collectives™ on Stack Overflow

Array value to check with comma separated values in table and return all row if single match with values

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related